Avatar of maroulator
maroulator
 asked on

MS Access - String to Date Conversion

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
Microsoft Access

Avatar of undefined
Last Comment
jerryb30

8/22/2022 - Mon
jerryb30

As a calculated value, format(DateSerial(right([Rating Start Date]), mid([rating start date], 3,2), left([rating start date], 2)), "mm/dd/yyyy")
ASKER CERTIFIED SOLUTION
jerryb30

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Andy Bogus

This is why I don't like access... Does it not have a "CONVERT" statement?
SOLUTION
als315

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jerryb30

^ty
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck