CDate and CStr issue with "Invalid Use of Null" Issue

[excellenthelp]

Hello Accessers,



I am experiencing an 'Invalid Use of Null' issue with this expression:

Anniversary Date: CDate(CStr(DatePart("m",[initialaccreddate]))+"/"+CStr(DatePart("d",[initialaccreddate]))+"/"+CStr(DatePart("yyyy",Now())))

On one database it works. But when I copy the same on another database and then the same expression does not work. It shows "Invalid use of Null" error.

The field initialaccreddate is the same on both databases.

What is the culprit of the issue?!

Your help will be thanked and a star will be given for your reputation!

-ME

[JoeM]

I would check the data in your initialaccreddate field in the database where you are getting the error. Do you have any Null values in the is field?
If so, it is causing errors for your function (i.e. you cannot pull a "month" or "date" component out of a date that does not exist).

[June7]

All of the conversion functions (CStr, CDate, CInt, etc) error with Null. If the field is Null, the expression errors. Does every record have data?

Number conversion functions will trigger a type mismatch error on empty string.

[excellenthelp]

Answer: For all of the conversion functions (CStr, CDate, CInt, etc.), make sure there are no null values in the field in the table it is deriving the data from. Simple rule and easy to remember.

Thanks JoeM and June7!

Marking as SOLVED.