L
Laurie
Hi...I'm new to Access.. Using the query design view, how do update a date
field that is in mm/dd/yyyy format to yyyymmdd?
Thank you!
field that is in mm/dd/yyyy format to yyyymmdd?
Thank you!
J
Jerry Whittle
If it's actually stored in a Date/Time field, you don't. Rather you format it
to look like you want. Dates are actually stored as numbers which don't look
anything like dates anyway. Today is stored at 40115 which is that many days
since 30 December 1899!
To make it LOOK like you want, use the Format function. In a query it would
look like this with the appropriate field where it says TheDateField .
TheDate: Format([TheDateField], "yyyymmdd")
to look like you want. Dates are actually stored as numbers which don't look
anything like dates anyway. Today is stored at 40115 which is that many days
since 30 December 1899!
To make it LOOK like you want, use the Format function. In a query it would
look like this with the appropriate field where it says TheDateField .
TheDate: Format([TheDateField], "yyyymmdd")
V
vanderghast
That is more about format than storage. As example, you don't really want to
change the bits and bytes in the computer, right? Same for date. Let the
dates (and time) be stored the way Access 'likes' them, and if that does not
match your needs, you can FORMAT the presentation. As example, in the
Immediate Window (Ctrl_G, and you should see a small window there, with its
caption title "Immediate"), type:
? FORMAT( now, "yyyymmdd" ) [ enter ]
20091029
(result may vary, if you do it on another date, for sure
)
So, define you field as date time and know your data is then in US format by
default and use a FORMAT if you wish another representation. Note that the
result of a format is technically a STRING, not a date, so you can't make
computation (such as difference in day between two 'dates' ) on the
formatted representation, while you can on a default date_time type
Vanderghast, Access MVP
change the bits and bytes in the computer, right? Same for date. Let the
dates (and time) be stored the way Access 'likes' them, and if that does not
match your needs, you can FORMAT the presentation. As example, in the
Immediate Window (Ctrl_G, and you should see a small window there, with its
caption title "Immediate"), type:
? FORMAT( now, "yyyymmdd" ) [ enter ]
20091029
(result may vary, if you do it on another date, for sure
)So, define you field as date time and know your data is then in US format by
default and use a FORMAT if you wish another representation. Note that the
result of a format is technically a STRING, not a date, so you can't make
computation (such as difference in day between two 'dates' ) on the
formatted representation, while you can on a default date_time type
Vanderghast, Access MVP
M
Marshall Barton
Laurie said:
In general that is a very bad idea. How a field's value is
stored has little or nothing to do with how it is displayed.
To view your data, you should create a form or report with
an appropriate layout and text boxes with their Format
property set to display the date however you want. Even if
you insist that you have to see the data in table view, you
can set the field's Format property to:
yyyymmdd
to display the value the way you want.