VBA ACCESS Comparing String as they are integer

Question

I am trying to prompt the user to input a range and display all the instruments that are within that range in a subform.

Problem: The upper and lower range is a text field (because some of the range cannot be expressed in integer). As seen in the screenshot, the comparison only compare the first character of the field.

User's input: 5 - 3 On the subform: 36 - 4 It compares 5 and 3 instead of 36

I know vba is doing what it has been told but how can I achieve the result I want?

Here is my code for requering the subform:

    Dim Up As Integer
    Dim Low As Integer

        If Me.Text_L = "" Or IsNull(Me.Text_L) Or Me.Text_U = "" Or IsNull(Me.Text_U) Then
            MsgBox ("Please choose a valid range!")
        Else
            Up = Me.Text_U
            Low = Me.Text_L

            SQL = SQL_Origin & " WHERE [qry_View_Search].[Upper_Range] <= '" & Up & "' " _
            & "AND [qry_View_Search].[Lower_Range] >= '" & Low & "';"


            subform_View_Search.Form.RecordSource = SQL
            subform_View_Search.Form.Requery
        End If

You are comparing them as strings, I think you needs to say [upper range]<=" & cint(up) & " AND [qry_view_search... — Nathan_Sav
– Nathan_Sav, Commented Mar 21, 2016 at 16:17
If you make the Upper and Lower fields a floating point datatype (eg Single or Double), instead of text, they can store decimal values. Then you can do numeric comparisons with actual numbers instead of text. — HansUp
– HansUp, Commented Mar 21, 2016 at 16:18
@HansUp Thats the thing, some of the data in this field cannot be expressed in integer. But despite of those data, I still want to compare the rest of the integer — Jason Chan
– Jason Chan, Commented Mar 21, 2016 at 16:52
Cast those strings to numbers when they represent valid numbers, or Null otherwise. So do comparisons similar to this: IIf(IsNumeric([Upper]), Val([Upper]), Null) <= 5 — HansUp
– HansUp, Commented Mar 21, 2016 at 17:31

Jason Chan · Accepted Answer · 2016-03-22 13:07:39Z

2

so what i did is made a new column in the query for IIf(IsNumeric([Upper]), Val([Upper]), Null) to get all the numeric result.

Then in the vba, I re query the subform as below

SQL = SQL_Origin & " WHERE [qry_View_Search].[Upper] <= cint(Forms![frm_View_Search]![Text_U]) " _
                                    & "AND [qry_View_Search].[Lower] >= cint(Forms![frm_View_Search]![Text_L]);"

Thanks @HansUp !

answered Mar 22, 2016 at 13:07

Jason Chan

4510 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gustav · Accepted Answer · 2016-03-21 18:59:27Z

0

I have successfully for those cases used Val only:

Value: Val([FieldName])

or:

Value: Val(Nz([FieldName]))

answered Mar 21, 2016 at 18:59

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Collectives™ on Stack Overflow

VBA ACCESS Comparing String as they are integer

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related