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

R

Ruth

I created a form to use as a "print menu" for a report. You can select
the date range (txtStartDate and txtStopDate). I have this part of the
code working. However, I also need to be able to filter based on the
job flag: [Flag] with value of 1, 2 or 3-- OR show all (still applying
the date range). I created a list box on the form and named it
[ReportFilter]. It's bound to the first column (hidden) with 0 for
All, 1, 2 and 3 to correspond with the Flag names. I can't figure out
how to get All records to show on top of not being able to incorporate
with the above date range.

Here's what I have so far. How would I modify?

Private Sub btnViewRpt_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptJobsbyCustInclCanc_All"
strField = "Callin"

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

' Debug.Print strWhere
DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub


Any help anyone can offer will be greatly appreciated!

Thanks,
Ruth
 
J

James A. Fortune

Ruth said:
I created a form to use as a "print menu" for a report. You can select
the date range (txtStartDate and txtStopDate). I have this part of the
code working. However, I also need to be able to filter based on the
job flag: [Flag] with value of 1, 2 or 3-- OR show all (still applying
the date range). I created a list box on the form and named it
[ReportFilter]. It's bound to the first column (hidden) with 0 for
All, 1, 2 and 3 to correspond with the Flag names. I can't figure out
how to get All records to show on top of not being able to incorporate
with the above date range.

Here's what I have so far. How would I modify?

Private Sub btnViewRpt_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptJobsbyCustInclCanc_All"
strField = "Callin"

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

' Debug.Print strWhere
DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub


Any help anyone can offer will be greatly appreciated!

Thanks,
Ruth
Click to expand...

Try this modified version (watch for line wrapping):

Private Sub btnViewRpt_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptJobsbyCustInclCanc_All"
strField = "Callin"

strWhere = ""
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] = " & Chr(34) &
CStr(Me!ReportFilter.Value) & Chr(34)
Else
strWhere = "[Flag] = " & Chr(34) & CStr(Me!ReportFilter.Value) &
Chr(34)
End If
End If
End If
' Debug.Print strWhere
If strWhere <> "" Then
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Else
DoCmd.OpenReport strReport, acViewPreview
End If
End Sub

James A. Fortune
(e-mail address removed)
 
R

Ruth

Thanks, James. I appreciate your help!

Adding the code, I was able to get it to run by adding another End If
right before End Sub (per debugger). I can get the report for "All"
but making any of the other selections gives this error:

Run-time error '3464':

Data type mismatch in criteria expression.

Suggestions?

Thanks ever so much for your help!

Ruth
 
J

James A. Fortune

Ruth said:
Thanks, James. I appreciate your help!

Adding the code, I was able to get it to run by adding another End If
right before End Sub (per debugger).
Click to expand...

I don't see where I missed any.
I can get the report for "All"
but making any of the other selections gives this error:

Run-time error '3464':

Data type mismatch in criteria expression.

Suggestions?

Thanks ever so much for your help!

Ruth
Click to expand...

If [Flag] is a numeric field then don't use the Chr(34) values. Compare
it directly to the number. E.g.,

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

I hope that helps. If not, post the code you are now running along with
the field types.

James A. Fortune
(e-mail address removed)
 
R

Ruth

On the missing EndIf, it was my fault! I either missed it in copying
or deleted it. I was going to post back when I realized it, but my
response hadn't posted to the forum yet. So-- you're right! You didn't
miss any.

I'm trying your suggestion now. I'll post back and let you know the
result. Thanks again for your help!
 
R

Ruth

Hi James,
It works beautifully! ... for three of my choices... *sigh*

The [Flag] field is numeric (contents of the field are 1, 2, 3 or
null). I can get the appropriate report back if the choice is a 1, 2
or 3, but now I can't get the "All" report that delivered everything
in the date range if Me!ReportFilter.Value was left blank.

Here's the current code:

Private Sub Command0_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"



strReport = "rptJobDetailSumm-ALL"
strField = "Callin"

strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
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
' Debug.Print strWhere
If strWhere <> "" Then
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Else
DoCmd.OpenReport strReport, acViewPreview
End If
End Sub


Thanks so very, very much!!!
Ruth
 
J

James A. Fortune

Ruth said:
Hi James,
It works beautifully! ... for three of my choices... *sigh*

The [Flag] field is numeric (contents of the field are 1, 2, 3 or
null). I can get the appropriate report back if the choice is a 1, 2
or 3, but now I can't get the "All" report that delivered everything
in the date range if Me!ReportFilter.Value was left blank.

Here's the current code:

Private Sub Command0_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"



strReport = "rptJobDetailSumm-ALL"
strField = "Callin"

strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
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
' Debug.Print strWhere
If strWhere <> "" Then
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Else
DoCmd.OpenReport strReport, acViewPreview
End If
End Sub


Thanks so very, very much!!!
Ruth
Click to expand...

I see that you made some changes to what I suggested. Plus, originally
you used 0 instead of Null for the 'All' case. What you want to have
happen is for there to be no restrictions whatever on [Flag] when the
'All' case is chosen. The Null case in the code I suggested was simply
to handle someone forgetting to choose a [Flag]. Anyway, make sure that
a [Flag] criterion doesn't become a part of strWhere at all for the
'All' case. Perhaps use your debug line to view what you are getting
for strWhere.

James A. Fortune
(e-mail address removed)
 
R

Ruth

James, I apologize for the changes I made. They were inadvertant.
However, here's the completed code and it works perfectly! Thanks
again so much for your help. If you're up for it, I have one more
variable I'd like to incorporate. I don't want to tread on your
goodwill, so please let me know if I can post a further question.

Thanks again!

Private Sub btnViewRpt_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"



strReport = "rptJobsbyCustInclCanc"
strField = "Callin"

strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
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
' Debug.Print strWhere
If strWhere <> "" Then
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Else
DoCmd.OpenReport strReport, acViewPreview

End If

End Sub
 
J

James A. Fortune

Ruth said:
James, I apologize for the changes I made. They were inadvertant.
However, here's the completed code and it works perfectly! Thanks
again so much for your help. If you're up for it, I have one more
variable I'd like to incorporate. I don't want to tread on your
goodwill, so please let me know if I can post a further question.

Thanks again!

Private Sub btnViewRpt_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"



strReport = "rptJobsbyCustInclCanc"
strField = "Callin"

strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
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
' Debug.Print strWhere
If strWhere <> "" Then
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Else
DoCmd.OpenReport strReport, acViewPreview

End If

End Sub
Click to expand...

O.K., I think I understand the changes you made enough to add another
variable to the mix.

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.

James A. Fortune
(e-mail address removed)
 
R

Ruth

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.

Re: 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.
Click to expand...

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

Thanks again for your help!

Ruth
 
J

James A. Fortune

Ruth said:
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.

Re: 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.
Click to expand...


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

Thanks again for your help!

Ruth
Click to expand...

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 before:

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
(e-mail address removed)
 
R

Ruth

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
 
J

James A. Fortune

Ruth said:
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
Click to expand...

Ruth,

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

James A. Fortune
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run time error 2
Allen Browne / Filter your report by more criteria 6
Trouble with Code for date filter on report... 9
Have A Form Open a Query 2
date error 1
Multiple Report Filter using form 1
Report Date Range 2
Please help w/form collecting parameters for Report 1

Top