Filter form on multiple combos with "between" Date Range

[Ruth]

I have a form format that I am using for a number of reports. Each
time I get it working (with help from folks on this forum!) the client
adds another parameter. :-/

The form filters a report.

Fields (combo boxes) on the form are:

cboStartDate
cboStopDate
cboCustName (CustID is the bound field but is not displayed in the
combo dropdown)
cboLocation

The trick here is that if any of the combo boxes don't have a
selection made, no filtering should be done on that selection.

I have tried to cobble together what I need from examples provided
online by Allen Browne. Unfortunately, I don't know what the heck I'm
doing, so I've probably made a mess of it. Here is the code (which
works through the between start date and end date part):

Private Sub PreviewCanceledJobsReport_Click()
On Error GoTo Err_PreviewCanceledJobsReport_Click
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "RptQryJobsCanceled"
strField = "Start"

If IsNull(Me.cboStartDate) Then
If Not IsNull(Me.cboStopDate) Then
strWhere = strField & " <= " & Format(Me.cboStopDate,
conDateFormat)
End If
Else
If IsNull(Me.cboStopDate) Then
strWhere = strField & " >= " & Format(Me.cboStartDate,
conDateFormat)
Else
strWhere = strField & " Between " & Format(Me.cboStartDate,
conDateFormat) _
& " And " & Format(Me.cboStopDate, conDateFormat) & " And "
End If
End If
If Not IsNull(Me.cboCustName) Then
strWhere = strWhere & "([CustomerID] = """ & _
Me![cboCustName].Value & """) AND "
End If

If Not IsNull(Me.cboLocation) Then
strWhere = strWhere & "([Location] = """ & _
Me![cboLocation].Value & """) AND "
End If


lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, 5)
End If


' Debug.Print strWhere
If strWhere <> "" Then
DoCmd.OpenReport strReport, acViewPreview, , strWhere
DoCmd.Maximize

Else
DoCmd.OpenReport strReport, acViewPreview
DoCmd.Maximize

End If
Exit Sub

Err_PreviewCanceledJobsReport_Click:
MsgBox Err.Description

End Sub
================================================

Any help sorting this out will be appreciated!

Thanks,
Ruth

 

[Carl Rapson]

I have a form format that I am using for a number of reports. Each
time I get it working (with help from folks on this forum!) the client
adds another parameter. :-/

The form filters a report.

Fields (combo boxes) on the form are:

cboStartDate
cboStopDate
cboCustName (CustID is the bound field but is not displayed in the
combo dropdown)
cboLocation

The trick here is that if any of the combo boxes don't have a
selection made, no filtering should be done on that selection.

I have tried to cobble together what I need from examples provided
online by Allen Browne. Unfortunately, I don't know what the heck I'm
doing, so I've probably made a mess of it. Here is the code (which
works through the between start date and end date part):

Private Sub PreviewCanceledJobsReport_Click()
On Error GoTo Err_PreviewCanceledJobsReport_Click
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "RptQryJobsCanceled"
strField = "Start"

If IsNull(Me.cboStartDate) Then
If Not IsNull(Me.cboStopDate) Then
strWhere = strField & " <= " & Format(Me.cboStopDate,
conDateFormat)
End If
Else
If IsNull(Me.cboStopDate) Then
strWhere = strField & " >= " & Format(Me.cboStartDate,
conDateFormat)
Else
strWhere = strField & " Between " & Format(Me.cboStartDate,
conDateFormat) _
& " And " & Format(Me.cboStopDate, conDateFormat) & " And "
End If
End If
If Not IsNull(Me.cboCustName) Then
strWhere = strWhere & "([CustomerID] = """ & _
Me![cboCustName].Value & """) AND "
End If

If Not IsNull(Me.cboLocation) Then
strWhere = strWhere & "([Location] = """ & _
Me![cboLocation].Value & """) AND "
End If


lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, 5)
End If


' Debug.Print strWhere
If strWhere <> "" Then
DoCmd.OpenReport strReport, acViewPreview, , strWhere
DoCmd.Maximize

Else
DoCmd.OpenReport strReport, acViewPreview
DoCmd.Maximize

End If
Exit Sub

Err_PreviewCanceledJobsReport_Click:
MsgBox Err.Description

End Sub
================================================

Any help sorting this out will be appreciated!

Thanks,
Ruth

You don't say what problem you're having. At first glance the code looks
like it should work. One question though: are the CustomerID and Location
values strings or numeric? If they're numeric, you need to remove the extra
quotes around the values:

If Not IsNull(Me.cboCustName) Then
strWhere = strWhere & "([CustomerID] = " & Me![cboCustName].Value &
" AND "
End If


Carl Rapson

 

[Ruth]

Hi Carl,
Thanks for your response! I worked all day yesterday on this trying to
get more specifics to get back to you.

First, Customer ID and Location are both text fields. (Airline
industry application so alpha codes for customers and locations.)

For clarification, my aim is to be able to return a wildcard result if
a selection is blank. (Current code does this for start and stop
fields.) For example, if I fill in start and stop dates and city, I
would get all jobs for all airlines. If I fill in start and stop date
and airline, I would get all jobs for all cities.

Also, I really would like to master the ability to continue to add
conditions to the code. (Why I liked the code from Allen as it looks
like you can just keep adding criteria!) It seems that every time I
finish one of these, they come back with an additional criteria they
want to filter on. They also like the whole "select all" if no choice
is made in the combo.

So-- back to the current problem. The code I posted before runs
without error but doesn't work. It doesn't return any records. If I
rem out lines as follows, I can get records within a data range
returned.
Private Sub PreviewCanceledJobsReport_Click()
On Error GoTo Err_PreviewCanceledJobsReport_Click
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "RptQryJobsCanceled"
strField = "Start"

If IsNull(Me.cboStartDate) Then
If Not IsNull(Me.cboStopDate) Then
strWhere = strField & " <= " & Format(Me.cboStopDate,
conDateFormat)
End If
Else
If IsNull(Me.cboStopDate) Then
strWhere = strField & " >= " & Format(Me.cboStartDate,
conDateFormat)
Else
strWhere = strField & " Between " & Format(Me.cboStartDate,
conDateFormat) _
& " And " & Format(Me.cboStopDate, conDateFormat)
'& " And "
End If
End If
' If Not IsNull(Me.cboCustName) Then
' strWhere = strWhere & "([CustomerID] = """ & _
' Me![cboCustName].Value & """) AND "
' End If
'
' If Not IsNull(Me.cboLocation) Then
' strWhere = strWhere & "([Location] = """ & _
' Me![cboLocation].Value & """) AND "
' End If
'
' lngLen = Len(strWhere) - 5 'Without trailing " AND "
' If lngLen > 0 Then
' strWhere = Left$(strWhere, 5)
' End If

Debug.Print strWhere
If strWhere <> "" Then
DoCmd.OpenReport strReport, acViewPreview, , strWhere
DoCmd.Maximize

Else
DoCmd.OpenReport strReport, acViewPreview
DoCmd.Maximize

End If
Exit Sub

Err_PreviewCanceledJobsReport_Click:
MsgBox Err.Description

End Sub


Many thanks for any suggestions you can offer!

Ruth

 

[Carl Rapson]

Hi Carl,
Thanks for your response! I worked all day yesterday on this trying to
get more specifics to get back to you.

First, Customer ID and Location are both text fields. (Airline
industry application so alpha codes for customers and locations.)

For clarification, my aim is to be able to return a wildcard result if
a selection is blank. (Current code does this for start and stop
fields.) For example, if I fill in start and stop dates and city, I
would get all jobs for all airlines. If I fill in start and stop date
and airline, I would get all jobs for all cities.

Also, I really would like to master the ability to continue to add
conditions to the code. (Why I liked the code from Allen as it looks
like you can just keep adding criteria!) It seems that every time I
finish one of these, they come back with an additional criteria they
want to filter on. They also like the whole "select all" if no choice
is made in the combo.

So-- back to the current problem. The code I posted before runs
without error but doesn't work. It doesn't return any records. If I
rem out lines as follows, I can get records within a data range
returned.
Private Sub PreviewCanceledJobsReport_Click()
On Error GoTo Err_PreviewCanceledJobsReport_Click
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "RptQryJobsCanceled"
strField = "Start"

If IsNull(Me.cboStartDate) Then
If Not IsNull(Me.cboStopDate) Then
strWhere = strField & " <= " & Format(Me.cboStopDate,
conDateFormat)
End If
Else
If IsNull(Me.cboStopDate) Then
strWhere = strField & " >= " & Format(Me.cboStartDate,
conDateFormat)
Else
strWhere = strField & " Between " & Format(Me.cboStartDate,
conDateFormat) _
& " And " & Format(Me.cboStopDate, conDateFormat)
'& " And "
End If
End If
' If Not IsNull(Me.cboCustName) Then
' strWhere = strWhere & "([CustomerID] = """ & _
' Me![cboCustName].Value & """) AND "
' End If
'
' If Not IsNull(Me.cboLocation) Then
' strWhere = strWhere & "([Location] = """ & _
' Me![cboLocation].Value & """) AND "
' End If
'
' lngLen = Len(strWhere) - 5 'Without trailing " AND "
' If lngLen > 0 Then
' strWhere = Left$(strWhere, 5)
' End If

Debug.Print strWhere
If strWhere <> "" Then
DoCmd.OpenReport strReport, acViewPreview, , strWhere
DoCmd.Maximize

Else
DoCmd.OpenReport strReport, acViewPreview
DoCmd.Maximize

End If
Exit Sub

Err_PreviewCanceledJobsReport_Click:
MsgBox Err.Description

End Sub


Many thanks for any suggestions you can offer!

Ruth

I still can't see anything wrong with your code on the surface, so it sounds
like it could be one of two things: a syntax error in your SQL code, or
there actually are no records matching your criteria. You're printing the
SQL statement in the Immediate window; does the SQL look OK? If you copy the
SQL code and paste it into a blank Query window in SQL view, does it return
the records you want?

One thing I might try is putting parentheses around the BETWEEN clause,
although I don't know if that would help.

Carl Rapson