CDate (1 Viewer)

[akb]

I currently have a query pulling the date field as a text field in the format yyyyddmm. I want it to convert to mmddyyyy. Ive accomplished this by using this:
ETA: Format(CDate(Format([PromiseDate],"0000-00-00")),"mm/dd/yyyy")


This works great except when there is nothing in the PromiseDate field - I then just get #Error.



Is there a way to just return as blank if there is nothing in the PromiseDate field rather than the #Error?



Thank you!

 

[namliam]

Iif(isnull(yourfield);null;yourformula)

Something like that

 

[akb]

I entered in this formula but I still receive the #Error. Thoughts?

Invoice Date: IIf(IsNull([InvPrtDate]),Null,Format(CDate(Format([InvPrtDate],"0000-00-00")),"mm/dd/yyyy"))

 

[namliam]

Well problem is, your text field, you cannot format the text field and it is probably screwing up your function...

To ensure proper working you will probably be better off using dateserial
Dateserial(Left(InvPrtDate,4), Mid(InvPrtDate,5,2),right(InvPrtDate,2)

Also you field may contain a ZLS (zero length string) lets try
Iif(nz(InvPrtDate,"") = "" ....