Changing date format for optional field

[kerkerak]

I need to do some date calculations on an optional date field that is current in YYYYMMDD text format.

I can get the date to format by using:
DateValue(Format(CLng([Date]),"0000-00-00"))

However, it returns an #Error if the field is blank.

I'm trying to write in the builder to leave the field blank if it is currently blank:
iif(IsNull[Date],"",(DateValue(Format(CLng([Date]),"0000-00-00")))

Must be something wrong with my syntax because it won't hold this second code. Any suggestions?

 

[NickHa]

IIf evaluates both Truepart and Falsepart before returning its result, so the error is in the CLng() where the source field is null. As far as I know, you can't do what you want with the empty field, so you may need a conventional If statement:
If not IsNull[Date] then x=DateValue(Format(CLng([Date]),"0000-00-00")) where x is the destination field.