Link to home
Start Free TrialLog in
Avatar of maroulator
maroulator

asked on

MS Access - String to Date Conversion (Part II)

To whom it may concern,

Referrencing the attached file, I have been able to succesfully convert a string with the format ddmmyyyy to a date with the format mm/dd/yyyy, using the formula below. In the attached file, this is shown by converting the value in the Start Date field to the value in the New Start Date field. My sticking point, however, is the last cell of the column whereby I need to convert a value of mmddyyyy - mmddyyyy to a value of mm/dd/yyyy - mm/dd/yyyy. What formula can I use to accommodate the entire column?

I think this is a tough one, so any insight is extremely appreciated.

-Maroulator

IIf([Start Date]="No Existing Value","No Existing Value",Format(DateSerial(Right([Start Date],4),Mid([Start Date],3,2),Left([Start Date],2)),"mm/dd/yyyy")) AS [New Start Date]
Example.xlsx
Avatar of Dodsworth
Dodsworth

hello

What version of Access?
Are you wanting to display this data in a form/continuous form or to update data?
IIf(InStr([start date],"-")>0,Format(DateSerial(Mid([Start Date],5,4),Mid([Start Date],3,2),Left([Start Date],2))) & " - " & Format(DateSerial(Right([Start Date],4),Mid([Start Date],Len([start date])-6,2),Mid([Start Date],Len([start date])-8,2))),IIf([Start Date]="No Existing Value","No Existing Value",Format(DateSerial(Right([Start Date],4),Mid([Start Date],3,2),Left([Start Date],2)),"mm/dd/yyyy"))) AS [new start date]
SOLUTION
Avatar of jerryb30
jerryb30
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of maroulator

ASKER

Dodsworth,

To answer your question, it's MS Access 2010 and I am looking to eventually display the data in a continuous form.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial