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
Solved

Format as date MS Access

Posted on 2013-06-13
6
619 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 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 49

Assisted Solution

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

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

/gustav
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 49

Expert Comment

by:Gustav Brock
ID: 39249108
You are welcome!

/gustav
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

860 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