IsNull Not Working For Date

[bhipwell via AccessMonster.com]

Hello,

I have a query established to provide data that populates a report. For
those fields that the user did not enter in any information, I want the
report to show the text "Not Entered." I have successfully created the
correct code in the query to handle all information except my birthday field,
which is in date format. Here is the query code:

Birthday: IIf(IsNull([EeBirthday]),"Not Entered",[EeBirthday])

EeBirthday is the field is should pull if it is not null. When I select
datasheet view from the design view, nothing happens. No errors, highlights,
nada. When I delete this particular field, query works fine. Here is a
sample query field that works just fine:

SocialSecurity: IIf(IsNull([EeSocialSecurity]),"Not Entered",
[EeSocialSecurity])

Am I missing something? Does it have to do with the date format the query
pulls from the table? I wouldn't think that it does since the social
security is in social security format.

Please help...

Thanks

B

 

[Allen Browne]

This is probably an issue with data types.

Presumably your EeBirthday is a Date/Time field if you open the table in
design view? If so, "Not entered" isn't a valid date value, so your
expression forces this field to be treated as text (not a date.)
Consequently, any filtering or sorting gives wrong results.

How about leaving the EeBirtthday field in the query exactly as it is. Then
in the report you use to print this, you can get the text box to show the
"Not Entered" bit. The Control Source of the text box would be:
=IIf(IsNull([EeBirthday]),"Not Entered", Format([EeBirthday], "Short
Date"))

(One assumes your EeSocialSecurity field is already a Text field.)

 

[bhipwell via AccessMonster.com]

Allen,

Thank you! I am not going that crazy. What I simply did was to remove the
date formating on the backend table. On the front end form where the
birthday is entered, I utilized an input mask here. The backend table stores
it as a short date since this is what the form specifies but does not retain
a hard coded date format. Works great! Thanks again.

B

 

[Rick Brandt]

Allen,

Thank you! I am not going that crazy. What I simply did was to
remove the date formating on the backend table. On the front end
form where the birthday is entered, I utilized an input mask here.
The backend table stores it as a short date since this is what the
form specifies but does not retain a hard coded date format. Works
great! Thanks again.

B

Then you are using a Text field to store characters that "look like" a date. If
you used an actual DateTime field then you would not have had this issue. True
DateTimes are stored exactly the same regardless of formatting or InputMasks
used. The same is not true about Text fields (at least with regard to
InputMasks).