Hi all,
I'm am trying to create a custom date string where a date exists for my field: [LastDistrictImportDate]. My date format alone works fine, but when I put it in the IIF statement, I get an #Error.

Where no date exists, the IIF statement is evaluating fine (to ""). Even if there's a better way to do this, I'd like to know what's wrong with what I've got...

Doesn't Work (where date exists):
Last Import Date: IIf([LastDistrictImportDate]>"",(Right("0" & Month([LastDistrictImportDate]),2) & "/" & Day([LastDistrictImportDate]) & "/" & Year([LastDistrictImportDate])),"")

Just the custom date part works fine:
aFormattedDate: Right("0" & Month([LastDistrictImportDate]),2) & "/" & Day([LastDistrictImportDate]) & "/" & Year([LastDistrictImportDate])

On Friday, July 27, 2012 1:00:50 PM UTC-4, Chris wrote:
OK, figured out my own fix. Changed if clause to: nz([LastDistrictImportDate],"") <> "". Not sure why it's a fix, probably has to do with data type.

"Chris" <chris_hollstein (AT) hotmail (DOT) com> wrote

It works because Null cannot be compared to anything by having NZ convert
the missing date to an empty string, you can compare it with another empty
string. A better way may be iif(not isdate([LastDistrictImportDate]), ....