Solved

MS Access - String to Date Conversion

Posted on 2012-12-24
5
346 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

757 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

16 Experts available now in Live!

Get 1:1 Help Now