check for empty value

[Brigitte P]

I have a report that has in the detail section several subreports and the
header and footer info is from another query. All works very well, except I
want users to be alerted when the failed to enter a report date that should
be displayed in the footer (it is based on a field in an underlying query,
but we don't want to make the field mandatory because the report date may be
later then the data entry date for the other fields). So users may go back
after completion of all other data entry and add the Report date once they
are ready for it.
I used something as simple as =IIf(IsNull([Report Date]),"Missing
Date",[Report Date]), tried this in code with msgBox, as an expression, and
in any other form I can think of. I put it in the ReportOpen Event and also
tried it in the OnFormat or OnPrint in the Footer, but it completely ignors
my code and just prints the report without the report date (when the report
date is entered, all is well). Ideally, it should come out
=IIf(IsNull([Report Date]),"Missing Date",Format[Report Date])"mmmm dd"",
""yyyy"); if date is missing cancels the report open event, and setfocus on
a command button in the selection form.
I also tried to put the code in the command button that opens the report,
but it also ignors me. Why? this seems so straight forward. Can anyone help?
Thanks.
Brigitte P.

 

[Marshall Barton]

I have a report that has in the detail section several subreports and the
header and footer info is from another query. All works very well, except I
want users to be alerted when the failed to enter a report date that should
be displayed in the footer (it is based on a field in an underlying query,
but we don't want to make the field mandatory because the report date may be
later then the data entry date for the other fields). So users may go back
after completion of all other data entry and add the Report date once they
are ready for it.
I used something as simple as =IIf(IsNull([Report Date]),"Missing
Date",[Report Date]), tried this in code with msgBox, as an expression, and
in any other form I can think of. I put it in the ReportOpen Event and also
tried it in the OnFormat or OnPrint in the Footer, but it completely ignors
my code and just prints the report without the report date (when the report
date is entered, all is well). Ideally, it should come out
=IIf(IsNull([Report Date]),"Missing Date",Format[Report Date])"mmmm dd"",
""yyyy"); if date is missing cancels the report open event, and setfocus on
a command button in the selection form.
I also tried to put the code in the command button that opens the report,
but it also ignors me. Why? this seems so straight forward. Can anyone help?

The report's Open event is too early to check values in the
Record source table/query and anywhere else if too late to
cancel the report. This means you should perform the check
in the form's command button's click event.

The question now becomes - Where does this data value come
from? You said it is included in the report's record source
query, but the form needs to get it from its source.

 

[Brigitte P]

Don't know what's happen in the group but someone had responded to my
request about the empty value and the reply is lost (or at least, I can't
find it any longer). However, I want to thank the person replying by
pointing me in a direction to go. I resolved the issue by setting criteria
in the underlying query (Report Date <>0) and then put the message in the No
Data Event. Works fine, even though I still don't know why my attempted
solution didn't work. According to Access help, the OpenEvent is run before
the source query is run (which doesn't make much sense to me). Nevertheless,
resolving it with query criteria works fine - as we in the country say:
"There are many ways to skin a cat" (Not that we skin our cats).
Thanks.
Brigitte P