Using parameters in reports.

J

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!
 
D

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
 
J

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


Duane Hookom said:
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


JudyB said:
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!
Click to expand...
Click to expand...
 
D

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

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


JudyB said:
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


Duane Hookom said:
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


JudyB said:
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!
Click to expand...
Click to expand...
Click to expand...
 
J

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


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

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


JudyB said:
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


Duane Hookom said:
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!
Click to expand...
Click to expand...
Click to expand...
 

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

Can you display parameters on reports? 4
printing report with parameters 1
Custom Parameter Dialogs for Reports 2
reports requiring parameters which shouldn't 2
Using field in a form to set parameters for a query 3
Using parameters with queries and reports 3
Opening reports prompts for parameter twice 2
Query parameters, charts, and reports in Access 2007 2

Top