Access reports and no value

[GMC]

Hello all,

Using Access 2000

I have a report that is based on a query. The query is pulling all records
within a certain date. Right now if there are no records within that date
period, the report comes up with #error in the spot where it counts the
number of people helped. What it doesn't do however, is add the dates I
input into the query. It also leaves those blank. What I would like is if
the report doesn't come up with any records, that it would put in the query
dates I have input.

Here is the query:

SELECT OSERVICE.OSID, OSERVICE.OSOFFICENUM, OSERVICE.OSPHELPED,
OSERVICE.OSDATEEND, OSERVICE.OSNOTE, OSERVICE.OSHOURS, OSERVICE.OSSNUM,
OSERVICE.OSFUNDSNUM, OSERVICE.OSEST, OSERVICE.OSEstNarrative,
OSERVICE.OMGoodStory, OSERVICE.OMGSNote, OSERVICE.OMPcode
FROM OSERVICE
WHERE (((OSERVICE.OSDATESTART) Between [Enter beginning date OPENED] And
[Enter ending date OPENED]) AND ((OSERVICE.OMCode)=106));

On the report I have two text fields, one says

[Enter beginning date OPENED]

and the other says [Enter ending date OPENED]

In those two fields I would like it to list the two dates I input when the
report asked for it.

Any suggestions?

Thank you.

 

[Guest]

Hi GMC,

You can achieve the desired effect by using a form to input your dates. Have
the query criteria point to the text boxes on your open form. You can also
prevent the #Error by using the Report's NoData event procedure to cancel
opening the report in the event that there is no data available. You'll need
to trap for error 2501 in the click event procedure for the command button on
the form, used to open the report. Here is a sample that you can try out:

http://home.comcast.net/~tutorme2/samples/customdialogbox.zip


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[GMC]

That was exactly what I needed. Thank you much.