Using parameters in reports.

[JudyB]

I have a report that opens three "Enter Parameter Value" windows. The first
window prompts the user for the "Area" and the second and third window
prompts the user for the "Beginning and End Dates." All of this is working
fine, however, if the user leaves the first "Enter Parameter Value" window
blank, I would like for the report to show ALL "Area" data listed in the
query. Any help is greatly appreciated. Thanks!

 

[Duane Hookom]

IMHO, parameter prompts are not a good user interface
http://www.tek-tips.com/faqs.cfm?fid=6763. I always use code to build a where
condition when fields are contained in the report's field list. For instance
a form with cboArea, txtStart, and txtEnd...

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboArea) Then
strWhere = strWhere & " And [Area] = """ & me.cboArea & """ "
End If
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " And [DateField]>=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " And [DateField]<=#" & Me.txtEnd & "# "
End If
DoCmd.OpenReport "rptYourRpt", acPreview , , strWhere

 

[JudyB]

I should have mentioned that I am new to Access. The information below may
be a little advanced for me. Is there an easier way as I do not really know
how or where to put the code you suggested?
--
JudyB

IMHO, parameter prompts are not a good user interface
http://www.tek-tips.com/faqs.cfm?fid=6763. I always use code to build a where
condition when fields are contained in the report's field list. For instance
a form with cboArea, txtStart, and txtEnd...

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboArea) Then
strWhere = strWhere & " And [Area] = """ & me.cboArea & """ "
End If
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " And [DateField]>=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " And [DateField]<=#" & Me.txtEnd & "# "
End If
DoCmd.OpenReport "rptYourRpt", acPreview , , strWhere
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I have a report that opens three "Enter Parameter Value" windows. The first
window prompts the user for the "Area" and the second and third window
prompts the user for the "Beginning and End Dates." All of this is working
fine, however, if the user leaves the first "Enter Parameter Value" window
blank, I would like for the report to show ALL "Area" data listed in the
query. Any help is greatly appreciated. Thanks!

 

[Duane Hookom]

The easiest method of implementing my solution is to create a form with the
text boxes and other controls as well as using the command button wizard to
write your code to open your report. Then modify the code as per my
suggestions.

If you want to continue to use parameter prompts and what to ignore the
criteria where a user doesn't enter a value, set the criteria to

= [Enter Start] Or [Enter Start] is Null

Again, you should consider kicking your apps up a notch
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I should have mentioned that I am new to Access. The information below may
be a little advanced for me. Is there an easier way as I do not really know
how or where to put the code you suggested?
--
JudyB

IMHO, parameter prompts are not a good user interface
http://www.tek-tips.com/faqs.cfm?fid=6763. I always use code to build a where
condition when fields are contained in the report's field list. For instance
a form with cboArea, txtStart, and txtEnd...

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboArea) Then
strWhere = strWhere & " And [Area] = """ & me.cboArea & """ "
End If
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " And [DateField]>=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " And [DateField]<=#" & Me.txtEnd & "# "
End If
DoCmd.OpenReport "rptYourRpt", acPreview , , strWhere
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I have a report that opens three "Enter Parameter Value" windows. The first
window prompts the user for the "Area" and the second and third window
prompts the user for the "Beginning and End Dates." All of this is working
fine, however, if the user leaves the first "Enter Parameter Value" window
blank, I would like for the report to show ALL "Area" data listed in the
query. Any help is greatly appreciated. Thanks!

 

[JudyB]

Thank you so much for your help. It works great. I hope to learn more in
regards to coding but was under a timeframe for this particular project.
Thanks Again!
--
JudyB

The easiest method of implementing my solution is to create a form with the
text boxes and other controls as well as using the command button wizard to
write your code to open your report. Then modify the code as per my
suggestions.

If you want to continue to use parameter prompts and what to ignore the
criteria where a user doesn't enter a value, set the criteria to

= [Enter Start] Or [Enter Start] is Null

Again, you should consider kicking your apps up a notch
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I should have mentioned that I am new to Access. The information below may
be a little advanced for me. Is there an easier way as I do not really know
how or where to put the code you suggested?
--
JudyB

IMHO, parameter prompts are not a good user interface
http://www.tek-tips.com/faqs.cfm?fid=6763. I always use code to build a where
condition when fields are contained in the report's field list. For instance
a form with cboArea, txtStart, and txtEnd...

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboArea) Then
strWhere = strWhere & " And [Area] = """ & me.cboArea & """ "
End If
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " And [DateField]>=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " And [DateField]<=#" & Me.txtEnd & "# "
End If
DoCmd.OpenReport "rptYourRpt", acPreview , , strWhere
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

I have a report that opens three "Enter Parameter Value" windows. The first
window prompts the user for the "Area" and the second and third window
prompts the user for the "Beginning and End Dates." All of this is working
fine, however, if the user leaves the first "Enter Parameter Value" window
blank, I would like for the report to show ALL "Area" data listed in the
query. Any help is greatly appreciated. Thanks!