Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

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
0
maroulator
Asked:
maroulator
  • 3
2 Solutions
 
jerryb30Commented:
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
 
jerryb30Commented:
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
 
corvetteguruCommented:
This is why I don't like access... Does it not have a "CONVERT" statement?
0
 
als315Commented:
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
 
jerryb30Commented:
^ty
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now