Solved

Format as date MS Access

Posted on 2013-06-13
6
623 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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.
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…

729 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