prompt for input when running a report

[Karen]

I'm trying to create a report that will prompt the user for a date. The
date needs to be used as part of the expression that follows.

=DCount("[reference]","[ComplaintReturn]","([ReturnCat]='Meter Repair' or
[ReturnCat]='Meter Check/Clean/Calibrate' or [ReturnCat]='Meter
Exchange/Upgrade') and [recdate] = date$() and [repairship] is Null")

I want the user to be able to enter yesterdays date or todays date,
depending on when they are running the report and get the number of records
with that date in the [recdate] field. So i want to replace 'and [recdate] =
date$()' with something to do that.

Any suggestions?

Karen

 

[Wayne Morgan]

How are you prompting for the date? If you use a pop-up form, you could
simply refer back to the form for the information. If you are using a
parameter in the query, you can refer to this parameter in the report. You
may have to set a textbox's control source to the parameter then refer to
the textbox in your equation.

Next, you'll need to concatenate the value into your equation.

=DCount("[reference]","[ComplaintReturn]","([ReturnCat]='Meter Repair' or
[ReturnCat]='Meter Check/Clean/Calibrate' or [ReturnCat]='Meter
Exchange/Upgrade') and [recdate] = #" & Forms!frmMyPopup!txtDateTextbox & "#
and [repairship] is Null")
Or
=DCount("[reference]","[ComplaintReturn]","([ReturnCat]='Meter Repair' or
[ReturnCat]='Meter Check/Clean/Calibrate' or [ReturnCat]='Meter
Exchange/Upgrade') and [recdate] = #" & [ParameterName] & "# and
[repairship] is Null")
Or
=DCount("[reference]","[ComplaintReturn]","([ReturnCat]='Meter Repair' or
[ReturnCat]='Meter Check/Clean/Calibrate' or [ReturnCat]='Meter
Exchange/Upgrade') and [recdate] = #" & txtDateTextbox & "# and [repairship]
is Null")

Try it without the # signs (date delimiters) if it doesn't work with them.

 

[Karen]

I ended up creating a unbound textbox (text19) and setting its control
source to '=[Enter the receiving date here]' and then using the following
for the count

=DCount("[reference]","[ComplaintReturn]","([ReturnCat]='Meter Repair' or
[ReturnCat]='Meter Check/Clean/Calibrate' or [ReturnCat]='Meter
Exchange/Upgrade') and [recdate] = text19 and [repairship] is Null")

It works great!
Thanks for your help.

How are you prompting for the date? If you use a pop-up form, you could
simply refer back to the form for the information. If you are using a
parameter in the query, you can refer to this parameter in the report. You
may have to set a textbox's control source to the parameter then refer to
the textbox in your equation.

Next, you'll need to concatenate the value into your equation.

=DCount("[reference]","[ComplaintReturn]","([ReturnCat]='Meter Repair' or
[ReturnCat]='Meter Check/Clean/Calibrate' or [ReturnCat]='Meter
Exchange/Upgrade') and [recdate] = #" & Forms!frmMyPopup!txtDateTextbox & "#
and [repairship] is Null")
Or
=DCount("[reference]","[ComplaintReturn]","([ReturnCat]='Meter Repair' or
[ReturnCat]='Meter Check/Clean/Calibrate' or [ReturnCat]='Meter
Exchange/Upgrade') and [recdate] = #" & [ParameterName] & "# and
[repairship] is Null")
Or
=DCount("[reference]","[ComplaintReturn]","([ReturnCat]='Meter Repair' or
[ReturnCat]='Meter Check/Clean/Calibrate' or [ReturnCat]='Meter
Exchange/Upgrade') and [recdate] = #" & txtDateTextbox & "# and [repairship]
is Null")

Try it without the # signs (date delimiters) if it doesn't work with them.

--
Wayne Morgan
Microsoft Access MVP

I'm trying to create a report that will prompt the user for a date. The
date needs to be used as part of the expression that follows.

=DCount("[reference]","[ComplaintReturn]","([ReturnCat]='Meter Repair' or
[ReturnCat]='Meter Check/Clean/Calibrate' or [ReturnCat]='Meter
Exchange/Upgrade') and [recdate] = date$() and [repairship] is Null")

I want the user to be able to enter yesterdays date or todays date,
depending on when they are running the report and get the number of
records
with that date in the [recdate] field. So i want to replace 'and [recdate]
=
date$()' with something to do that.

Any suggestions?

Karen