Parameter Query and a report

[Guest]

I am working on a database for investments. The customer would like to be
able to enter a date range and see which investments are due to mature. I've
set up a paramter query for this. The problem is when there is no
investments they would still like a report to print out that has the date
range entered by a message that says "no investments for this time period"
and no data.

I'm hoping somebody can help. I'm sure this is probably an easy fix but
I've search all over for the answer and don't seem to be finding it.!

Thanks

 

[Mike Revis]

Not sure if this will work for you but if you look at the report property
sheet there is an "On No Data" event.

Mike

 

[fredg]

I am working on a database for investments. The customer would like to be
able to enter a date range and see which investments are due to mature. I've
set up a paramter query for this. The problem is when there is no
investments they would still like a report to print out that has the date
range entered by a message that says "no investments for this time period"
and no data.

I'm hoping somebody can help. I'm sure this is probably an easy fix but
I've search all over for the answer and don't seem to be finding it.!

Thanks

1) You'll need to use a form to enter the parameters in, not the
parameter prompt you get from 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 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"

Add a Label to your report.
"No investments for the period of " & forms!ParamForm!StartDate & "
and " & forms!ParamForm!EndDate & " whatever else you wish to
display."
Make this label not visible.
Name it "lblNoData"

Code the Report's OnNoData event:
lblNoData.Visible = True

When ready to run the report, open the report.
The form will open and wait for the selection of the Company and the
entry of the starting and ending dates wanted.
Click the command button and then report will run.
If there is no data the label will print.
When the report closes, it will close the form.