Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Format as date MS Access

Posted on 2013-06-13
6
Medium Priority
?
628 Views
Last Modified: 2013-06-14
Can someone please provide me with the code for me to put it in a Microsoft access query to format a date?

the current format is as follow: 06132003

I like to format it as 06/13/2003 but the data is stored in the text field with the above format.

I like to change it with a query and make a new table with the new date format..

I just need the code to be able to that...
0
Comment
Question by:TonyEF
6 Comments
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 1600 total points
ID: 39246080
Is it a Date field or a Text field?  If it is Text, you can convert it to a date using the CDate function and some String functions:

CDate(Left([TextDate],2) & "/" & Mid([TextDate],3,2) & "/" & Right([TextDate],4))

Save the Date value to a Date field, and set its format as desired in the Format field.
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 300 total points
ID: 39246367
This should work too:
CDate(Format([TextDate]","##/##/####")
0
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
ID: 39246699
Or - without typos:

RealDate: CDate(Format([YourTextDate],"@@/@@/@@@@"))

/gustav
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:IrogSinta
ID: 39247090
Correction:
CDate(Format([TextDate],"##/##/####")
Thanks for pointing that out, gustav
0
 

Author Closing Comment

by:TonyEF
ID: 39248902
thank you all...
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39249108
You are welcome!

/gustav
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

877 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