Access 2000 - Cdate with IIF

Dear Experts,

I have a field which has text format in Access, it can have three kind of values

- text "CommonCode"
- text "NotInRSD"
- texts like "2012-02-22"

My target would to convert the third scenario to date format with CDate, in the other two cases it should stay as it is.

I have tried this formula but this does not seem converting the date kind of text to date

DateFormatted: IIf([Date_]="CommonCode" Or [Date_]="NotInRSD";[Date_];CDate([Date_]))

thanks,
LVL 1
csehzIT consultantAsked:
Who is Participating?
 
als315Connect With a Mentor Commented:
Sorry, different regional settings. Correct:
IIf(IsDate([Date_]),CDate([Date_]),Null)
0
 
Rey Obrero (Capricorn1)Commented:
try

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

or this

DateFormatted: IIf(isdate([Date_]), CDate([Date_]),[Date_])
0
 
als315Commented:
You can't store in one field text and date values. You can add field with date, where will be converted date
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
csehzIT consultantAuthor Commented:
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.

0
 
csehzIT consultantAuthor Commented:
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
0
 
csehzIT consultantAuthor Commented:
Or also would be proper to me having some fictitious date instead of the #Error, for example 2015-01-01
0
 
GRayLCommented:
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.
0
 
csehzIT consultantAuthor Commented:
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.

0
 
als315Commented:
May be better to use 1900-01-01, but you think in right direction
0
 
als315Commented:
Or you can put NULL:
IIf(IsDate([Date_]);CDate([Date_]);Null)
0
 
Rey Obrero (Capricorn1)Commented:
csehz,

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

i thought you are just using it in a query...
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
if that is the case then use

try

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

or this

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


or

DateFormatted: IIf(isdate([Date_]); CDate([Date_]);#1/1/1900#)

or this

DateFormatted: IIf(isdate([Date_]), CDate([Date_]),#1/1/1900#)

0
 
csehzIT consultantAuthor Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
the poster that modified the original code, got the accepted answer and the original got an assist. what an irony...
0
 
csehzIT consultantAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.