Error in query parameter when printing a report

[Guest]

Hi I am very new to using access, but I have created a database with a query
parameter for date and I run a report from this query on a weekly basis. To
show the date in the report I added this particular command: "="Between " &
[Enter Start Date] & " and " & [Enter End Date]" in a text box and it works
great.

The problem I am having is that I need to run the report on a weekly basis
and when 0 records are received for a week, I get a "#error" where the date
usually is. It seems to only happen if there are no records for the week. I
tested this by adding a false record and the report prints fine and shows the
correct date range. I would like to print the date even if there are no
records. Anyone know how to do that?

Thank you in advance for the help

 

[fredg]

Hi I am very new to using access, but I have created a database with a query
parameter for date and I run a report from this query on a weekly basis. To
show the date in the report I added this particular command: "="Between " &
[Enter Start Date] & " and " & [Enter End Date]" in a text box and it works
great.

The problem I am having is that I need to run the report on a weekly basis
and when 0 records are received for a week, I get a "#error" where the date
usually is. It seems to only happen if there are no records for the week. I
tested this by adding a false record and the report prints fine and shows the
correct date range. I would like to print the date even if there are no
records. Anyone know how to do that?

Thank you in advance for the help

You can do this, but not if the parameter prompts are in the query.

Create an unbound form.

Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the query's date field write:
Between forms!Paramform!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

In the report header, add an unbound text control
Set it's control source to:
="Between " & [Forms!ParamForm!StartDate] & " and " &
[Forms!ParamForm!EndDate]

When ready to run the report, open the report.
The form will open and wait for the entry of the starting and ending
dates wanted.
Click the command button and then report will run.
When the report closes, it will close the form.
The starting and ending dates will appear on the report.