June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.
Course Of The Month
6 days, 7 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
MS Access - String to Date Conversion
Posted on 2012-12-24
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([Ratin
Any help would be appreciated.
Many thanks in advance,
-Maroulator
Example-File.xlsx
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([Ratin
Any help would be appreciated.
Many thanks in advance,
-Maroulator
Example-File.xlsx
[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
3
5 Comments
As a calculated value, format(DateSerial(right([R
IIf([Rating Start Date]="No Existing Value","No Existing Value", format(DateSerial(right([R
Active today
Small correction to jerryb30's code: IIf([Rating Start Date]="No Existing Value","No Existing Value", format(DateSerial(right([R
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
AutoNumbers should increment automatically, without duplicates. But sometimes something goes wrong, and the next AutoNumber value is a duplicate. This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses
Course of the Month6 days, 7 hours left to enroll
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.