MS Access - String to Date Conversion

maroulator
maroulator used Ask the Experts™
on
To whom it may concern,

I currently have the text variable [Rating Start Date] which assumes one of two values: a) Either a value of "No Existing Value", or b) a date value of ddmmyyyy.

What I am trying to do is retain the values of "No Existing Value"  but convert all of ddmmyyyy formats into mm/dd/yyyy formats. Below is the formula that I've been using. I also offer samples of current and desired output in the attached Excel file.

IIf([Rating Start Date]="No Existing Value","No Existing Value",CDate(Format([Rating Start Date],"mm/dd/yyyy"))) AS Test

Any help would be appreciated.

Many thanks in advance,

-Maroulator
Example-File.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
jerryb30

Commented:
As a calculated value, format(DateSerial(right([Rating Start Date]), mid([rating start date], 3,2), left([rating start date], 2)), "mm/dd/yyyy")
Commented:
IIf([Rating Start Date]="No Existing Value","No Existing Value", format(DateSerial(right([Rating Start Date]), mid([rating start date], 3,2), left([rating start date], 2)), "mm/dd/yyyy")) as Test
corvetteguruDBA/Systems Analyist

Commented:
This is why I don't like access... Does it not have a "CONVERT" statement?
als315
Commented:
Small correction to  jerryb30's code: IIf([Rating Start Date]="No Existing Value","No Existing Value", format(DateSerial(right([Rating Start Date],4), mid([Rating Start Date], 3,2), left([Rating Start Date], 2)), "mm/dd/yyyy")) as Test
jerryb30

Commented:
^ty

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial