Link to home
Create AccountLog in
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
Avatar of jerryb30
jerryb30
Flag of United States of America image

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
Avatar of jerryb30
jerryb30
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
This is why I don't like access... Does it not have a "CONVERT" statement?
SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account