Filter Continuous form with 2 combo boxes and 2 text date fields

[Sierras]

Hi

I have a continuous form and want to filter it with a a couple of unbound
combo boxes and a couple of unbound text boxes for the date range.
The user selects the filter criteria and then presses a filter button.
So far, I have managed to get the combo boxes to work but can't figure out
how to add the date range to the code.
This is what I have so far:

Private Sub Command40_Click()

Dim sFilter As String

sFilter = ""

If Not IsNull(ComboPatient) And ComboPatient <> 0 Then
sFilter = "[Patient] = """ & Me.ComboPatient & """"
End If

If Not IsNull(ComboProvider) And ComboProvider <> 0 Then
sFilter = IIf(sFilter <> "", sFilter & " and ", sFilter)
sFilter = sFilter & "[Provider] = """ & Me.ComboProvider & """"
End If

If sFilter <> "" Then
Me.Filter = sFilter
Me.FilterOn = True
End If

End Sub

The unbound date text boxes are FromDate and ToDate and their getting
their data from a date field called DateOfService.

Anyone know how I could add this criteria to my code?

Thanks

 

[Allen Browne]

See:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

The code is identical for a form, and illustrates how to handle the cases
where the user enters both dates, starting date only, ending date only, or
neither dates.

You will need to add " AND " to the end of your sFilter if you are filtering
on patient as well.

If you need a another example to download and pull apart, this one includes
a date range in combination with other text boxes and combos:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I have a continuous form and want to filter it with a a couple of unbound
combo boxes and a couple of unbound text boxes for the date range.
The user selects the filter criteria and then presses a filter button.
So far, I have managed to get the combo boxes to work but can't figure out
how to add the date range to the code.
This is what I have so far:

Private Sub Command40_Click()

Dim sFilter As String

sFilter = ""

If Not IsNull(ComboPatient) And ComboPatient <> 0 Then
sFilter = "[Patient] = """ & Me.ComboPatient & """"
End If

If Not IsNull(ComboProvider) And ComboProvider <> 0 Then
sFilter = IIf(sFilter <> "", sFilter & " and ", sFilter)
sFilter = sFilter & "[Provider] = """ & Me.ComboProvider & """"
End If

If sFilter <> "" Then
Me.Filter = sFilter
Me.FilterOn = True
End If

End Sub

The unbound date text boxes are FromDate and ToDate and their getting
their data from a date field called DateOfService.

Anyone know how I could add this criteria to my code?

Thanks

 

[Sierras]

On Sat, 18 Nov 2006 23:06:36 -0500, Allen Browne

Well I've tried both examples, but I still can't get it to work. This is
what I've got, but the result gives a blank form if I put anything in the
date fields.

Private Sub Command40_Click()

Dim sFilter As String

sFilter = ""

If Not IsNull(ComboPatient) And ComboPatient <> 0 Then
sFilter = "[Patient] = """ & Me.ComboPatient & """"


End If

If Not IsNull(ComboProvider) And ComboProvider <> 0 Then
sFilter = IIf(sFilter <> "", sFilter & " and ", sFilter)
sFilter = sFilter & "[Provider] = """ & Me.ComboProvider & """"

End If


If IsNull(Me.FromDate) Then
If Not IsNull(Me.FromDate) Then 'End date, but no start.
sFilter = sFilter & DateOfService & " <= " & Me.ToDate
End If
Else
If IsNull(Me.ToDate) Then 'Start date, but no End.
sFilter = sFilter & DateOfService & " >= " & Me.FromDate
Else 'Both start and end dates.
sFilter = sFilter & DateOfService & " Between " & Me.FromDate _
& " And " & Me.ToDate
End If
End If


If sFilter <> "" Then
Me.Filter = sFilter
Me.FilterOn = True
End If

End Sub

 

[Allen Browne]

You have omitted the # delimiters around the literal date values, e.g.

sFilter = "DateOfService <= " & Format(Me.ToDate, "\#mm\/dd\/yyyy\#")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

On Sat, 18 Nov 2006 23:06:36 -0500, Allen Browne

Well I've tried both examples, but I still can't get it to work. This is
what I've got, but the result gives a blank form if I put anything in the
date fields.

Private Sub Command40_Click()

Dim sFilter As String

sFilter = ""

If Not IsNull(ComboPatient) And ComboPatient <> 0 Then
sFilter = "[Patient] = """ & Me.ComboPatient & """"


End If

If Not IsNull(ComboProvider) And ComboProvider <> 0 Then
sFilter = IIf(sFilter <> "", sFilter & " and ", sFilter)
sFilter = sFilter & "[Provider] = """ & Me.ComboProvider & """"

End If


If IsNull(Me.FromDate) Then
If Not IsNull(Me.FromDate) Then 'End date, but no start.
sFilter = sFilter & DateOfService & " <= " & Me.ToDate
End If
Else
If IsNull(Me.ToDate) Then 'Start date, but no End.
sFilter = sFilter & DateOfService & " >= " & Me.FromDate
Else 'Both start and end dates.
sFilter = sFilter & DateOfService & " Between " & Me.FromDate _
& " And " & Me.ToDate
End If
End If


If sFilter <> "" Then
Me.Filter = sFilter
Me.FilterOn = True
End If

End Sub

 

[Sierras]

OK, I've almost got it working.
I can get the two combos to work by themselves and now (thanks to you), I
can get the two date filters working by themselves.
But I can't bet them to work together.
Whenever I try to filter on both the a combo and a date, I get this error:
"Can't assign a value to this object"
Any ideas what's missing?
Here's the code. Thanks....

Private Sub Command40_Click()

Dim sFilter As String

sFilter = ""

If Not IsNull(ComboPatient) And ComboPatient <> 0 Then
sFilter = "[Patient] = """ & Me.ComboPatient & """"


End If

If Not IsNull(ComboProvider) And ComboProvider <> 0 Then
sFilter = IIf(sFilter <> "", sFilter & " and ", sFilter)
sFilter = sFilter & "[Provider] = """ & Me.ComboProvider & """"

End If


If IsNull(Me.FromDate) Then
If Not IsNull(Me.ToDate) Then 'End date, but no start.
sFilter = sFilter & "DateOfService <= " & Format(Me.ToDate,
"\#dd\/mm\/yyyy\#")
End If

Else
If IsNull(Me.ToDate) Then 'Start date, but no End.
sFilter = sFilter & "DateOfService >= " & Format(Me.FromDate,
"\#dd\/mm\/yyyy\#")
Else 'Both start and end dates.

sFilter = sFilter & "DateOfService" & " Between " &
Format(Me.FromDate, "\#dd\/mm\/yyyy\#") _
& " And " & Format(Me.ToDate, "\#dd\/mm\/yyyy\#")
End If
End If



If sFilter <> "" Then
Me.Filter = sFilter
Me.FilterOn = True
End If

End Sub

 

[Allen Browne]

If you can get them both working individually, perhaps you would like to use
2 strings:
Dim sFilter1 As String
Dim sFilter2 As String

Assign the dates to one string, and the patient combo to the other.
Then combine them:
If (sFilter1 <> vbNullString) And (sFilter2 <> vbNullString) Then
sFilter = "(" & sFilter1 & ") AND (" & sFilter2 & ")"
End If

To help you debug your code, print the value of the strings to the Immediate
Window, like this:
Debug.Print sFilter
Then when it fails, press Ctrl+G to see what the string contains.
You can then see how to fix it up.

The filter string must contain something that would work in the WHERE clause
of a query. To see an example, you can mock up a query using any old
criteria, and then switch it to SQL view (View menu in query design.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

OK, I've almost got it working.
I can get the two combos to work by themselves and now (thanks to you), I
can get the two date filters working by themselves.
But I can't bet them to work together.
Whenever I try to filter on both the a combo and a date, I get this error:
"Can't assign a value to this object"
Any ideas what's missing?
Here's the code. Thanks....

Private Sub Command40_Click()

Dim sFilter As String

sFilter = ""

If Not IsNull(ComboPatient) And ComboPatient <> 0 Then
sFilter = "[Patient] = """ & Me.ComboPatient & """"


End If

If Not IsNull(ComboProvider) And ComboProvider <> 0 Then
sFilter = IIf(sFilter <> "", sFilter & " and ", sFilter)
sFilter = sFilter & "[Provider] = """ & Me.ComboProvider & """"

End If


If IsNull(Me.FromDate) Then
If Not IsNull(Me.ToDate) Then 'End date, but no start.
sFilter = sFilter & "DateOfService <= " & Format(Me.ToDate,
"\#dd\/mm\/yyyy\#")
End If

Else
If IsNull(Me.ToDate) Then 'Start date, but no End.
sFilter = sFilter & "DateOfService >= " & Format(Me.FromDate,
"\#dd\/mm\/yyyy\#")
Else 'Both start and end dates.

sFilter = sFilter & "DateOfService" & " Between " &
Format(Me.FromDate, "\#dd\/mm\/yyyy\#") _
& " And " & Format(Me.ToDate, "\#dd\/mm\/yyyy\#")
End If
End If



If sFilter <> "" Then
Me.Filter = sFilter
Me.FilterOn = True
End If

End Sub

 

[Sierras]

I finally got it working. Thanks for your help.
Now all I got to do is figure out how I can print a report with all the
parameters that the user filtered on the form. But I'll try to use the
example you gave and hope to figure it out that way.

So this is the final code that worked.
Thanks again..

---------

Private Sub Command40_Click()

Dim sFilter As String

sFilter = ""

If Not IsNull(ComboPatient) And ComboPatient <> 0 Then
sFilter = "[Patient] = """ & Me.ComboPatient & """"


End If

If Not IsNull(ComboProvider) And ComboProvider <> 0 Then
sFilter = IIf(sFilter <> "", sFilter & " and ", sFilter)
sFilter = sFilter & "[Provider] = """ & Me.ComboProvider & """"

End If


If IsNull(Me.FromDate) Then
If Not IsNull(Me.ToDate) Then 'End date, but no start.
sFilter = IIf(sFilter <> "", sFilter & " and ", sFilter)
sFilter = sFilter & "DateOfService <= " & Format(Me.ToDate,
"\#dd\/mm\/yyyy\#")
End If

Else
If IsNull(Me.ToDate) Then 'Start date, but no End.
sFilter = IIf(sFilter <> "", sFilter & " and ", sFilter)
sFilter = sFilter & "DateOfService >= " & Format(Me.FromDate,
"\#dd\/mm\/yyyy\#")
Else 'Both start and end dates.

sFilter = IIf(sFilter <> "", sFilter & " and ", sFilter)
sFilter = sFilter & "DateOfService" & " Between " &
Format(Me.FromDate, "\#dd\/mm\/yyyy\#") _
& " And " & Format(Me.ToDate, "\#dd\/mm\/yyyy\#")
End If
End If



If sFilter <> "" Then
Me.Filter = sFilter
Me.FilterOn = True
End If

End Sub