469,923 Members | 1,605 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,923 developers. It's quick & easy.

Converting dates

I need help converting dates. Currently, I have several tables and
databases that use the format 1/20/2007. I have a table right now
that uses the 20070120 format, and I was hoping that someone could
help me convert this. Thanks!

May 3 '07 #1
3 2595
GH
Are you trying to change how that table stores the date or do you need
the date reformatted for display purposes? You can always create a
date string using the MID, RIGHT, and LEFT functions, such as DateFmt:
DateValue(Mid([Date],5,2) & "/" & Right([Date],2) & "/" & Left([Date],
4)). If you want the table store changed, you can always add a new
field with the formatted date and perform an update query that
converts the date using the above function. I am not sure if this is
what you are looking for, so please post again if you need specific
behavior.

- GH

On May 3, 10:27 am, zsh...@riteaid.com wrote:
I need help converting dates. Currently, I have several tables and
databases that use the format 1/20/2007. I have a table right now
that uses the 20070120 format, and I was hoping that someone could
help me convert this. Thanks!

May 3 '07 #2
So you have a Number (or Text?) field that contains values such as:
20070120
for 2007, Jan, 20.

Create a query.
Type an expression like this into the Field row:
CVDate(Format([Field1], "0000\/00\/00"))
replacing Field1 with the name of this field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<zs****@riteaid.comwrote in message
news:11**********************@n59g2000hsh.googlegr oups.com...
>I need help converting dates. Currently, I have several tables and
databases that use the format 1/20/2007. I have a table right now
that uses the 20070120 format, and I was hoping that someone could
help me convert this. Thanks!
May 3 '07 #3
zs****@riteaid.com wrote:
I need help converting dates. Currently, I have several tables and
databases that use the format 1/20/2007. I have a table right now
that uses the 20070120 format, and I was hoping that someone could
help me convert this. Thanks!
Look at the field properties and look to see if they are actual dates
and just change the field properties. Date fields can be stored the
same and the field properties is what makes them look different. This
may not really be feasible if you already have other reports that need
the date in a different form.

You should be able to do a query and just make a computed column using a
function or something to reformat the date.

http://office.microsoft.com/en-us/ac...CH062526711033
May 4 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Kit | last post: by
reply views Thread by Massimo Fiorentino | last post: by
6 posts views Thread by Jim Davis | last post: by
3 posts views Thread by Scott Kinney | last post: by
12 posts views Thread by Vincent Delporte | last post: by
11 posts views Thread by Keith Hughitt | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.