Access 2000 - Cdate with IIF

try

DateFormatted: IIf(isdate([Date_]); CDate([Date_]);[Date_])

or this

DateFormatted: IIf(isdate([Date_]), CDate([Date_]),[Date_])

You can't store in one field text and date values. You can add field with date, where will be converted date

Thanks I have tried both, but unfortunately it is still text.

Basically this formula works for me - DateFormatted: CDate([Date_]) - converting the date text to date, but bringing #Error for the other two scenarios.

Als315 thanks, do you have maybe idea then how to keep the dates texts as date and maybe applying blank for the other two ones?

So being blank where now it brings the CDate value #Error

Or also would be proper to me having some fictitious date instead of the #Error, for example 2015-01-01

Because you cannot make the field DateFormatted either a datetime datatype or a string datatype -  it is either one or the other - as stated by als315.

GRayL yes but could be maybe applied a kind of iserror for such case?

So IIf the Cdate get error then just give some fictitious date like 2015-01-01, or can be even blank, and if the Cdate is successful without error, in that case should be that result.

May be better to use 1900-01-01, but you think in right direction

Or you can put NULL:
IIf(IsDate([Date_]);CDate([Date_]);Null)

csehz,

are you storing the dateformatted to a field in a table?

i thought you are just using it in a query...

Als315, I have used your formula on my machine like this

Expr2: IIf(IsDate([Date_]);CDate([Date_]);Null) and works perfect.

Capricorn, also your formulas working, I applied this version

Expr5: IIf(IsDate([Date_]);CDate([Date_]);#1900-01-01#)

Thanks very much the help, you are great, so closing the topic

the poster that modified the original code, got the accepted answer and the original got an assist. what an irony...

Capricorn sorry but on my machine has to be ; instead of the , and sometimes manually has to change. However you prepared both, just I am an amateur and can confuse a lot things :-)

Basically I still does not understand what is the difference of this two

1) Your first ; version post was like this

Expr6: IIf(IsDate([Date_]);CDate([Date_]);[Date_])

and this is still text for dates, and CommonCode and NotInRSD text without #Error.

2) In your last posting in my understanding this is around the same

Expr7: IIf(IsDate([Date_]);CDate([Date_]);Null)

and this works perfectly for my targets. Date for dates and blank for the texts.

But still considering why, but you know I could think on this even for two weeks :-))) For me just great that it works now this 2) formula.

Anyway I am using this in a query, so this text field is in a query, and based on that applying your formula