By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,224 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,224 IT Pros & Developers. It's quick & easy.

Date Conversion

P: n/a
Is there a way in Access to convert a date in format "mdy - 08/05/2005"
to "ymd - 2005-08-05" using an update query?

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
No. You cannot alter the date with an update query, because internally
Access stores the date/time data as a floating point number, where the
integer part represents the date and the fraction represents the time of
day. The format is applied at display time, so it does not make sense to
talk about altering the stored data to match a display.

The data is displayed to the user according to how they have dates defined
in Regional Settings in the Windows Control Panel. You could therefore alter
your settings there, and Access would display the dates that way. Of course
the change would affect all your other well-behaved Windows programs as
well.

If that is not acceptable, you can use the Format property of the field in
the table to specify how the data should be displayed. Or, you could even
set the Format property of the field in the query.

Note that literal date/time values in VBA code or SQL statements is NOT
affected by your regional settings. For more info on that and the 3 cases
where Access tends to misunderstand dates, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
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.

"Paul" <pa**@tdsd.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
Is there a way in Access to convert a date in format "mdy - 08/05/2005"
to "ymd - 2005-08-05" using an update query?

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.