Display Report with Form-Based Parameters

  • Thread starter Thread starter Scott A
  • Start date Start date
S

Scott A

I have a report that is based on a query. I would like
to use a form that lets the user select the start and end
dates for the report.

I've tried entering the form information into the query
grid like this...

Between #[Forms]![myform]![txtStartDate]# And #[Forms]!
[myform]![txtEndDate]#

....but I get messages that tell me my syntax is invalid.

So I'm back to the drawing board and looking for some
advice on the following:

1. What is the best way to pass the dates entered in the
form to the query as valid parameter values?

2. How to program the 'Apply' button on the form so that
it a) passes the parameters to the query and b)displays
the report with the parameters applied.

Thanks!

Scott
 
Scott said:
I have a report that is based on a query. I would like
to use a form that lets the user select the start and end
dates for the report.

I've tried entering the form information into the query
grid like this...

Between #[Forms]![myform]![txtStartDate]# And #[Forms]!
[myform]![txtEndDate]#

...but I get messages that tell me my syntax is invalid.

So I'm back to the drawing board and looking for some
advice on the following:

1. What is the best way to pass the dates entered in the
form to the query as valid parameter values?
Click to expand...

What you have is very close, just drop the #s (they're only
needed when the date is a string.

2. How to program the 'Apply' button on the form so that
it a) passes the parameters to the query and b)displays
the report with the parameters applied.
Click to expand...

With the above criteria in the query, all the button code
needs to do is open the report. The button wizard will
generate the code for you.

I prefer an alternative approach. Remove the parameter
criteria from the query so, if you open the report from the
database window, it displays all the records. Then code the
button to use the OpenReport method's WhereCondition
argument something like this:

stDocName = "thereportname"
stWhere = "Between " & _
Format(Me!txtStartDate, "\#m\/d\/yyyy\#") & _
" And " & _
Format(Me!txtEndDate, "\#m\/d\/yyyy\#")

DoCmdOpenReport stDocName,acViewPreview, _
WHereCondition:= stWhere

You will probably want to add more code to guard against
invalid dates, missing dates, etc.
 
Michael,

What are the advantages/disadvantages to putting the where
clause in the query or in the code.

Thanks for the alternate method
Jim
-----Original Message-----
Scott said:
I have a report that is based on a query. I would like
to use a form that lets the user select the start and end
dates for the report.

I've tried entering the form information into the query
grid like this...

Between #[Forms]![myform]![txtStartDate]# And #[Forms]!
[myform]![txtEndDate]#

...but I get messages that tell me my syntax is invalid.

So I'm back to the drawing board and looking for some
advice on the following:

1. What is the best way to pass the dates entered in the
form to the query as valid parameter values?
Click to expand...

What you have is very close, just drop the #s (they're only
needed when the date is a string.

2. How to program the 'Apply' button on the form so that
it a) passes the parameters to the query and b)displays
the report with the parameters applied.
Click to expand...

With the above criteria in the query, all the button code
needs to do is open the report. The button wizard will
generate the code for you.

I prefer an alternative approach. Remove the parameter
criteria from the query so, if you open the report from the
database window, it displays all the records. Then code the
button to use the OpenReport method's WhereCondition
argument something like this:

stDocName = "thereportname"
stWhere = "Between " & _
Format(Me!txtStartDate, "\#m\/d\/yyyy\#") & _
" And " & _
Format(Me!txtEndDate, "\#m\/d\/yyyy\#")

DoCmdOpenReport stDocName,acViewPreview, _
WHereCondition:= stWhere

You will probably want to add more code to guard against
invalid dates, missing dates, etc.
Click to expand...
 
Jim & Marshall - Thanks for the tip

I'm going to stick with the form with the parameter query
because the form provides a calendar control that ensures
valid date entries (ah, users)...

Now my only (as if!) problem is getting rid of the dang
form! I'm passing the correct date parameters to the
report, but the form won't get out of the way!

I've tried adding DoCmd Close in both the OnClick (for
the button) and OnLostFocus (for the form) events, but it
won't budge...

Help?
-----Original Message-----
Scott said:
I have a report that is based on a query. I would like
to use a form that lets the user select the start and end
dates for the report.

I've tried entering the form information into the query
grid like this...

Between #[Forms]![myform]![txtStartDate]# And #[Forms]!
[myform]![txtEndDate]#

...but I get messages that tell me my syntax is invalid.

So I'm back to the drawing board and looking for some
advice on the following:

1. What is the best way to pass the dates entered in the
form to the query as valid parameter values?
Click to expand...

What you have is very close, just drop the #s (they're only
needed when the date is a string.

2. How to program the 'Apply' button on the form so that
it a) passes the parameters to the query and b)displays
the report with the parameters applied.
Click to expand...

With the above criteria in the query, all the button code
needs to do is open the report. The button wizard will
generate the code for you.

I prefer an alternative approach. Remove the parameter
criteria from the query so, if you open the report from the
database window, it displays all the records. Then code the
button to use the OpenReport method's WhereCondition
argument something like this:

stDocName = "thereportname"
stWhere = "Between " & _
Format(Me!txtStartDate, "\#m\/d\/yyyy\#") & _
" And " & _
Format(Me!txtEndDate, "\#m\/d\/yyyy\#")

DoCmdOpenReport stDocName,acViewPreview, _
WHereCondition:= stWhere

You will probably want to add more code to guard against
invalid dates, missing dates, etc.
Click to expand...
 
Jim/Chris said:
Michael,
Click to expand...

Who's Michael? ;-)

What are the advantages/disadvantages to putting the where
clause in the query or in the code.
Click to expand...

The reason I prefer to use the OpenReport method's
WhereCondition argument is that the report and its query are
not tied to the form. The report can then be used in
different ways from different forms.

Thanks for the alternate method
Click to expand...

Any time Jerry ;')
--
Marsh
MVP [MS Access]


-----Original Message-----
Scott said:
I have a report that is based on a query. I would like
to use a form that lets the user select the start and end
dates for the report.

I've tried entering the form information into the query
grid like this...

Between #[Forms]![myform]![txtStartDate]# And #[Forms]!
[myform]![txtEndDate]#

...but I get messages that tell me my syntax is invalid.

So I'm back to the drawing board and looking for some
advice on the following:

1. What is the best way to pass the dates entered in the
form to the query as valid parameter values?
Click to expand...

What you have is very close, just drop the #s (they're only
needed when the date is a string.

2. How to program the 'Apply' button on the form so that
it a) passes the parameters to the query and b)displays
the report with the parameters applied.
Click to expand...

With the above criteria in the query, all the button code
needs to do is open the report. The button wizard will
generate the code for you.

I prefer an alternative approach. Remove the parameter
criteria from the query so, if you open the report from the
database window, it displays all the records. Then code the
button to use the OpenReport method's WhereCondition
argument something like this:

stDocName = "thereportname"
stWhere = "Between " & _
Format(Me!txtStartDate, "\#m\/d\/yyyy\#") & _
" And " & _
Format(Me!txtEndDate, "\#m\/d\/yyyy\#")

DoCmdOpenReport stDocName,acViewPreview, _
WHereCondition:= stWhere

You will probably want to add more code to guard against
invalid dates, missing dates, etc.
Click to expand...
Click to expand...
 
Scott said:
Jim & Marshall - Thanks for the tip

I'm going to stick with the form with the parameter query
because the form provides a calendar control that ensures
valid date entries (ah, users)...

Now my only (as if!) problem is getting rid of the dang
form! I'm passing the correct date parameters to the
report, but the form won't get out of the way!

I've tried adding DoCmd Close in both the OnClick (for
the button) and OnLostFocus (for the form) events, but it
won't budge...
Click to expand...

You can't close the form until the query is done using the
parameters. Make the form invisible instead.
--
Marsh
MVP [MS Access]


-----Original Message-----
Scott said:
I have a report that is based on a query. I would like
to use a form that lets the user select the start and end
dates for the report.

I've tried entering the form information into the query
grid like this...

Between #[Forms]![myform]![txtStartDate]# And #[Forms]!
[myform]![txtEndDate]#

...but I get messages that tell me my syntax is invalid.

So I'm back to the drawing board and looking for some
advice on the following:

1. What is the best way to pass the dates entered in the
form to the query as valid parameter values?
Click to expand...

What you have is very close, just drop the #s (they're only
needed when the date is a string.

2. How to program the 'Apply' button on the form so that
it a) passes the parameters to the query and b)displays
the report with the parameters applied.
Click to expand...

With the above criteria in the query, all the button code
needs to do is open the report. The button wizard will
generate the code for you.

I prefer an alternative approach. Remove the parameter
criteria from the query so, if you open the report from the
database window, it displays all the records. Then code the
button to use the OpenReport method's WhereCondition
argument something like this:

stDocName = "thereportname"
stWhere = "Between " & _
Format(Me!txtStartDate, "\#m\/d\/yyyy\#") & _
" And " & _
Format(Me!txtEndDate, "\#m\/d\/yyyy\#")

DoCmdOpenReport stDocName,acViewPreview, _
WHereCondition:= stWhere

You will probably want to add more code to guard against
invalid dates, missing dates, etc.
Click to expand...
Click to expand...
 
Michael is short for Marsh, right?

Anyhow, Mike:

I'm a little rusty on my superpowers. I make the form
invisible how?

Scott (but you can call me Steve)
-----Original Message-----
Scott said:
Jim & Marshall - Thanks for the tip

I'm going to stick with the form with the parameter query
because the form provides a calendar control that ensures
valid date entries (ah, users)...

Now my only (as if!) problem is getting rid of the dang
form! I'm passing the correct date parameters to the
report, but the form won't get out of the way!

I've tried adding DoCmd Close in both the OnClick (for
the button) and OnLostFocus (for the form) events, but it
won't budge...
Click to expand...

You can't close the form until the query is done using the
parameters. Make the form invisible instead.
--
Marsh
MVP [MS Access]


-----Original Message-----
Scott A wrote:

I have a report that is based on a query. I would like
to use a form that lets the user select the start and end
dates for the report.

I've tried entering the form information into the query
grid like this...

Between #[Forms]![myform]![txtStartDate]# And # [Forms]!
[myform]![txtEndDate]#

...but I get messages that tell me my syntax is invalid.

So I'm back to the drawing board and looking for some
advice on the following:

1. What is the best way to pass the dates entered in the
form to the query as valid parameter values?

What you have is very close, just drop the #s (they're only
needed when the date is a string.


2. How to program the 'Apply' button on the form so that
it a) passes the parameters to the query and b) displays
the report with the parameters applied.

With the above criteria in the query, all the button code
needs to do is open the report. The button wizard will
generate the code for you.

I prefer an alternative approach. Remove the parameter
criteria from the query so, if you open the report
Click to expand...
from
the
database window, it displays all the records. Then
Click to expand...
code
the
button to use the OpenReport method's WhereCondition
argument something like this:

stDocName = "thereportname"
stWhere = "Between " & _
Format(Me!txtStartDate, "\#m\/d\/yyyy\#") & _
" And " & _
Format(Me!txtEndDate, "\#m\/d\/yyyy\#")

DoCmdOpenReport stDocName,acViewPreview, _
WHereCondition:= stWhere

You will probably want to add more code to guard against
invalid dates, missing dates, etc.
Click to expand...
Click to expand...

.
Click to expand...
 
Scott said:
Michael is short for Marsh, right?

Anyhow, Mike:

I'm a little rusty on my superpowers. I make the form
invisible how?
Click to expand...

The form is opened as a pop up so it stays on top of the
report, right? I don't know why you want the form to be pop
up, but if that's what you're doing, so be it.

When the form opens the report is a good time for the form
to make itself invisible:

Me.Visible = False
DoCmd.OpenReport . . .

This leaves the form open without it cluttering up the
screen. Be sure to close the form (or make it visible) when
the report is finished, probably in the report's Close
event:

DoCmd.Close acForm, "theform"
or
Forms!theform.Visible = True

Scott (but you can call me Steve)
Click to expand...
<smile>
--
Marsh
MVP [MS Access]

 
Thanks, Marsh. I really do appreciate the help.

Scott
-----Original Message-----
Scott said:
Michael is short for Marsh, right?

Anyhow, Mike:

I'm a little rusty on my superpowers. I make the form
invisible how?
Click to expand...

The form is opened as a pop up so it stays on top of the
report, right? I don't know why you want the form to be pop
up, but if that's what you're doing, so be it.

When the form opens the report is a good time for the form
to make itself invisible:

Me.Visible = False
DoCmd.OpenReport . . .

This leaves the form open without it cluttering up the
screen. Be sure to close the form (or make it visible) when
the report is finished, probably in the report's Close
event:

DoCmd.Close acForm, "theform"
or
Forms!theform.Visible = True

Scott (but you can call me Steve)
Click to expand...
<smile>
--
Marsh
MVP [MS Access]


.
Click to expand...
 
Back
Top