Set date range and filter results via form/menu for Report Preview

Ruth wrote:
I'm sorry for being confusing! I actually have 4 menus for different
reports and I'm using the same code to drive all of them. Which was
going to take me to my next "variable" question... whether I could
populate "strReport" with a value from the menu. I think I probably
can....

But instead of doing that, I'd like to add two more combo boxes as
variables. cboCustomer and cboAircraftType. I've gotten close (I
think) but my syntax is still off. Both are text fields. They are on
the form below the ReportFilter field.

your edit below, thank you! I had changed out the string when I
was making the Chr(34) change and didn't notice until it was too late!
Thanks again for catching that.
| | | |
v v v v

But change the first strWhere to:

strWhere = "[Flag] = " & CStr(Nz(Me!ReportFilter.Value, 0))

so that you don't get an error if the user forgets to make a choice.



************************************************** *********************

Thanks again for your help!

Ruth


Although it's possible to open one of several different reports from
your code depending on which values the user selects, one of the
beauties of creating SQL dynamically is that several different scenarios
can be handled by a single report. For example, you can set the Control
Source for a text box on the report to something like:

="Report Flag: " & IIf(Not
IsNull([Forms]![frmReportFilter]!ReportFilter.Column(1)),
[Forms]![frmReportFilter]!ReportFilter.Column(1), "All")

Since you always have the [Flag] criterion in strWhere, you can tack on
cboCustomer and cboAircraftType to the SQL criteria if they contain values:

If Not IsNull(cbxCustomer.Value) Then
strWhere = strWhere & " AND [Customer] = " & Chr(34) &
cbxCustomer.Value & Chr(34)
End If
If Not IsNull(cbxAircraftType.Value) Then
strWhere = strWhere & " AND [AircraftType] = " & Chr(34) &
cbxAircraftType.Value & Chr(34)
End If

or if you have ID's in column 0 as befo

If Not IsNull(cbxCustomer.Column(1)) Then
strWhere = strWhere & " AND [Customer] = " & Chr(34) &
cbxCustomer.Column(1) & Chr(34)
End If
If Not IsNull(cbxAircraftType.Column(1)) Then
strWhere = strWhere & " AND [AircraftType] = " & Chr(34) &
cbxAircraftType.Column(1) & Chr(34)
End If

Obviously, [Customer] and [AircraftType] should be changed to your
actual field names if they are different. You can double check the
final strWhere using your debug statement. You can also write out
strWhere to a text file so that you can test out the SQL separately in
the Query By Example (QBE) query design area by creating a new query,
clicking on the SQL toolbar, then pasting the text for your query:

SELECT * FROM MyReportTable WHERE ...;

.... = strWhere text

James A. Fortune

Well darn. I KNOW I replied to this message this morning, but
evidently my post was eaten for breakfast!

James, I'm missing something. The code runs without error, debugging
doesn't show anything, but the additional combo boxes don't filter the
data any further.

Here's the code as I have it currently:

Private Sub Command0_Click()

Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "rptJobDetailSumm"
strField = "Callin"
strWhere = "[Flag] = " & CStr(Nz(Me!ReportFilter.Value, 0))
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then
strWhere = strField & " = " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
strWhere = strField & " = " & Format(Me.txtStartDate,
conDateFormat)
Else
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
If Not IsNull(Me!ReportFilter.Value) Then
If Me!ReportFilter.Value 0 Then
If strWhere "" Then
strWhere = "(" & strWhere & ") AND [Flag] = " & CStr(Me!
ReportFilter.Value)
Else
strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
End If
End If
End If
' Code works to this point

If Not IsNull(cboCustomer.Column(1)) Then
strWhere = strWhere & " AND [CustomerName] = " & Chr(34) &
cboCustomer.Column(1) & Chr(34)

End If
If Not IsNull(cboACType.Column(1)) Then
strWhere = strWhere & " AND [AircraftType] = " & Chr(34) &
cboACType.Column(1) & Chr(34)
End If
' End new code addition

' Debug.Print strWhere
If strWhere "" Then
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Else
DoCmd.OpenReport strReport, acViewPreview
End If
Exit Sub

' Debug.Print strWhere
If strWhere "" Then
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Else
DoCmd.OpenReport strReport, acViewPreview
End If
Exit Sub

End Sub


Regards,
Ruth

Ruth wrote:
Well darn. I KNOW I replied to this message this morning, but
evidently my post was eaten for breakfast!

James, I'm missing something. The code runs without error, debugging
doesn't show anything, but the additional combo boxes don't filter the
data any further.

Here's the code as I have it currently:

Private Sub Command0_Click()

Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "rptJobDetailSumm"
strField = "Callin"
strWhere = "[Flag] = " & CStr(Nz(Me!ReportFilter.Value, 0))
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then
strWhere = strField & " = " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
strWhere = strField & " = " & Format(Me.txtStartDate,
conDateFormat)
Else
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
If Not IsNull(Me!ReportFilter.Value) Then
If Me!ReportFilter.Value 0 Then
If strWhere "" Then
strWhere = "(" & strWhere & ") AND [Flag] = " & CStr(Me!
ReportFilter.Value)
Else
strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
End If
End If
End If
' Code works to this point

If Not IsNull(cboCustomer.Column(1)) Then
strWhere = strWhere & " AND [CustomerName] = " & Chr(34) &
cboCustomer.Column(1) & Chr(34)

End If
If Not IsNull(cboACType.Column(1)) Then
strWhere = strWhere & " AND [AircraftType] = " & Chr(34) &
cboACType.Column(1) & Chr(34)
End If
' End new code addition

' Debug.Print strWhere
If strWhere "" Then
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Else
DoCmd.OpenReport strReport, acViewPreview
End If
Exit Sub

' Debug.Print strWhere
If strWhere "" Then
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Else
DoCmd.OpenReport strReport, acViewPreview
End If
Exit Sub

End Sub


Regards,
Ruth


Ruth,

Try it with an additional comma before strWhere. For an explanation,
see Access Help for OpenReport.

James A. Fortune