Null Date value display in Microsoft Access 2007

[Owl]

#Error in Empty Date field

In a database for Disciplinary Hearings, where the ValidityEndDate is calculated depending on the entry in WarningType and thus NoMonths and the entry in ValidityStartDate.

If there is a date entered in ValidityStartDate it works perfectly.

When there is no entry in the field, ValidityStartDate, this causes #Error to be displayed in the field ValidityEndDate. I don’t want that #Error to be displayed. I understand that the problem exists because of multiplying by zero, but I need to know how to resolve it e.g. with Conditional Formatting if all else fails.



My criteria for the calculated query field (ValidityEndDate) are as follows:

CDate(DateAdd("m",([NoMonths]),([ValidityStartDate])))

Thank you in anticipation.

[alansidman]

Not sure I get the whole issue, but it sounds like you may need to use the NZ function in your calculation. Look here for information on that.

http://www.techonthenet.com/access/f...dvanced/nz.php

Alan

[Owl]

Thank you Alan. However, I had tried every variable of the Nz function that I could think of - and when you posted your reply, I tried the ones suggested in the link, but none of them made any difference.

I may be wrong, but it seems to me that you can't use Nz in a Date field.

[alansidman]

Owl;
Take a look at this link and see if this helps. Just another way of looking at nulls in date field.

http://forums.aspfree.com/microsoft-...on-219928.html

Alan

[Owl]

Oh Alan, you STAR!

In that link, I saw that there was no CDate and when I took mine out, it worked like a dream!

Thank you!

[alansidman]

You are welcome.