Solved

MS Access - String to Date Conversion

Posted on 2012-12-24
5
356 Views
Last Modified: 2012-12-25
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
0
Comment
Question by:maroulator
  • 3
5 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 38719533
As a calculated value, format(DateSerial(right([Rating Start Date]), mid([rating start date], 3,2), left([rating start date], 2)), "mm/dd/yyyy")
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 250 total points
ID: 38719537
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
0
 
LVL 2

Expert Comment

by:corvetteguru
ID: 38719574
This is why I don't like access... Does it not have a "CONVERT" statement?
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 250 total points
ID: 38719635
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
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38719723
^ty
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now