CODEWITHSUNDEEP
sqlvbams-accesscomparisonms-access-2007
Jason Chan
Jason Chan

Reputation: 45

VBA ACCESS Comparing String as they are integer

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?

enter image description here

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

Upvotes: 1

Views: 1575

Answers (2)

Jason Chan
Jason Chan

Reputation: 45

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 !

Upvotes: 2

Gustav
Gustav

Reputation: 55816

I have successfully for those cases used Val only:

Value: Val([FieldName])

or:

Value: Val(Nz([FieldName]))

Upvotes: 0

Related Questions