Searching for a Range

[OxDavis]

I am looking to develop a form that shows records in a report based upon criteria defined in said form. So far I have had success in filtering city names, state, and whether or not the client is a medicaid recipient. Unfortunately, my knowledge does not include how to search a range, such as "Find all records where the age of the client is between 20 and 25" Do I need to define 2 variables and than tell the filter to find all numbers in between those two numbers? What is the most expedient path? Here is what I have so far:

Private Sub cmdApplyFilter_Click()
Dim strCity As String
Dim strState As String
Dim strFilter As String
Dim strMedicaid As String

'Define Option Group
Select Case Me.fraMedicaid.Value
    Case 1
        strMedicaid = "='Y'"
    Case 2
        strMedicaid = "='N'"
    Case 3
        strMedicaid = "Like '*'"
End Select

'Define ComboBoxes and Filter

If IsNull(Me!cboCity.Value) Then
    strCity = "Like '*'"
    Else: strCity = "='" & Me!cboCity.Value & "'"
End If

If IsNull(Me!cboState.Value) Then
    strState = "Like '*'"
    Else: strState = "='" & Me!cboState.Value & "'"
End If

strFilter = "[ChildCity] " & strCity & " AND [StateAbb] " & strState & " AND [CurrMedicaid] " & strMedicaid


'Turn on Filter

With Reports![rptMain]
         .Filter = strFilter
         .FilterOn = True
    End With
    

    
    End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
     Reports![rptMain].FilterOn = False

End Sub

 

[DB7]

you could try either...

If IsNull(Me!cboAgeFrom.Value) Then
strAge = "Like '*'"
Else: strAge = "Between " & Me!cboAgeFrom.Value & " AND " & Me!cboAgeTo.Value
End If

Or something like...

If IsNull(Me!cboAgeFrom.Value) Then
strAge = "Like '*'"
Else: strAge1 = ">= " & Me!cboAgeFrom.Value : strAge2 = "<= " & Me!cboAgeTo.Value

 

[Bat17]

You might like to look at this site for information on Access SQL> I have found it quite useful
http://eis.bris.ac.uk/~ccmjs/acc97-r5.htm

Peter

 

[OxDavis]

Ok, I used your code and and also a hint from a previous poster and it wrked like a charm, I thank you. As I am a VBA novice, (knowing but a little code and some obvious functions), could you please inform me why Age is a string and not an integer? Is it because you have added "BETWEEN" and "AND" to the value of strAge?

 

[DB7]

OxDavis, No, it was an oversight on my part.
You're right, it should be,
Dim intAge As Integer...