Solved

MS Access - String to Date Conversion

Posted on 2012-12-24

Microsoft Access

2 solutions

Medium Priority

396 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

Comment

Question by:maroulator

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

5 Comments

LVL 26

Expert Comment

by:jerryb30

ID: 387195332012-12-24

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

LVL 26

Accepted Solution

by:jerryb30

jerryb30 earned 1000 total points

ID: 387195372012-12-24

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

LVL 2

Expert Comment

by:corvetteguru

ID: 387195742012-12-24

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

LVL 40

Assisted Solution

by:als315

als315 earned 1000 total points

ID: 387196352012-12-24

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

LVL 26

Expert Comment

by:jerryb30

ID: 387197232012-12-24

^ty

Featured Post

Question has a verified solution.

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

William Moulton Marston’s DISC Matrix applied to Email Etiquette

Article by: Dermot

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…

Automating Microsoft Excel from Access (Part II)

Article by: Dale Fye

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.

MS Access – Cascading Combos

Video by: Jim

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…

Microsoft Access

Access Desktop Databases – A Quick Tour

Video by: Jim

What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.