IIF expression and #Error message

[Sandova]

I am a beginner level Access user. I imported an Excel spreadsheet with the my dates fields showing as text. I created a query to reformat so that they show in date format. However, when the field is blank, I get an #Error showing. How do I get it remove this message and just show a blank? Also, I am also trying to retain the field name as the column label and it will not allow. Is there a better way to make this happen?



Table date format is text 10-19-2015. The column label I need is the field name, Start Date. If I remove the label the column returns with Expr 1 label.
Below is my expression

Start Date: IIf([Start Date] Is Null,"",Format(CDate([Start Date]),"mm/dd/yyyy"))

Hopefully someone can assist me in combining two expressions to get the results I need.

[June7]

Why is the field a text type?

Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention.

AFAIK, an alias name cannot be the same as a native field name. Why should it matter if name is different from [Start Date]? You can change labels in form or report to show whatever you want.

CDate() function errors on Null. It is not even needed in this expression. However, it is not the cause of error in this case.

Try:

DateStart: Format([Start Date],"mm/dd/yyyy")

Format() function actually returns a string value, not a true date. If you need a true date value, the Format() function will not serve. Try:

DateStart: IIf([Start Date] Is Null, Null, CDate([Start Date]))

[InsuranceGuy]

I believe CDate will also return an error if a non-date string is passed to it, such as CDate("asdf").

You may want to try:

IIF(IsDate([Start Date]),cdate([Start Date]),null)

Cheers,

Jeff