Solved

Format as date MS Access

Posted on 2013-06-13
6
621 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
[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
6 Comments
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 400 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 75 total points
ID: 39246367
This should work too:
CDate(Format([TextDate]","##/##/####")
0
 
LVL 50

Assisted Solution

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

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

/gustav
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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 50

Expert Comment

by:Gustav Brock
ID: 39249108
You are welcome!

/gustav
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

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.

Join & Ask a Question