VBA Code to Search for Numerical Range instead of Date

[Lavenderchan]

Hi,

I am using Allen Brown Search criteria to filter and return records that I need. However I need to do a numerical range instead of a date range. I am not finding any information online to help me. I have a column that lists a number of units and I want to be able to search for a range of them. Any suggestions would be helpful.

Keri

 

[SkipVought]

Hi,

Maybe an example?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Lavenderchan]

Here is an example:

'Another Text Field Example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtfromunit) Then
strWhere = strWhere & "([Total Unit Count] Like ""*" & Me.txtfromunit & "*"") AND "
End If

'Another Text Field Example. Use Like to find anywhere in the field.
If Not IsNull(Me.txttounit) Then
strWhere = strWhere & "([Total Unit Count] Like ""*" & Me.txttounit & "*"") AND "
End If

I have it as a text field how every its not pulling the number range I need. I need to now how to return the numerical range I need.

Thanks.

 

[MajP]

You need to use a BETWEEN operator

https://www.w3schools.com/sql/sql_between.asp

Something like.
"where [Total Unit Count] BETWEEN " & Me.txtfromunit " AND " & Me.txttounit

 

[Lavenderchan]

Since this is using vba code does between and really work?

 

[MajP]

The Filter property is a SQL string expression consisting of a WHERE clause without the WHERE keyword

 

[Lavenderchan]

Thanks for you help I solved it by using this:

'Number Field example.
If Not IsNull(Me.txtfromunit) Then
strWhere = strWhere & "([Total Unit Count] >=" & Me.txtfromunit & ") AND "
End If

'Another Number Field Example.
If Not IsNull(Me.txttounit) Then
strWhere = strWhere & "([Total Unit Count] <= " & Me.txttounit & ") AND "
End If

It is pulling the ranges I need.