Timestamps

[scstrachan]

Does anyone know how to pull the date and the time from a timestamp
field in msaccess? Here is an example.

8/30/2007 11:53:00 AM

Ultimately, I would like to be able to place the date in one field and
the time in another. i would then like to be able to sort the time.
Any help would be appreciated.

Thanks,
Scott

[Ed Robichaud]

Date/time fields are actually serial numbers formatted to display either
date, time or both. If you're storing the values with "general" format,
just change the format (in your form/query/report) to one of the available
time formats and use that to sort on. You will probably want to sort on
date+time to accommodate values over multiple 24hr periods.
-Ed
<sc********@gmail.comwrote in message
news:11**********************@50g2000hsm.googlegro ups.com...

Does anyone know how to pull the date and the time from a timestamp
field in msaccess? Here is an example.

8/30/2007 11:53:00 AM

Ultimately, I would like to be able to place the date in one field and
the time in another. i would then like to be able to sort the time.
Any help would be appreciated.

Thanks,
Scott

[Salad]

sc********@gmail.com wrote:

Does anyone know how to pull the date and the time from a timestamp
field in msaccess? Here is an example.

8/30/2007 11:53:00 AM

Ultimately, I would like to be able to place the date in one field and
the time in another. i would then like to be able to sort the time.
Any help would be appreciated.

Thanks,
Scott

Here's an example. Using format, the value is a string. Cdate converts
it back to a date field.
? dt
8/31/2007 7:06:12 AM
dt = Now()
? format(dt,"mm/dd/yyyy")
08/31/2007
? format(dt,"hh:nn:ss")
07:06:55
? cdate(format(dt,"mm/dd/yyyy"))
8/31/2007
? cdate(format(dt,"hh:nn:ss"))
7:06:55 AM

[John Mishefske]

sc********@gmail.com wrote:

Does anyone know how to pull the date and the time from a timestamp
field in msaccess? Here is an example.

8/30/2007 11:53:00 AM

Ultimately, I would like to be able to place the date in one field and
the time in another. i would then like to be able to sort the time.
Any help would be appreciated.

You've received some other suggestions; here is another.

You said "timestamp field" so to me that indicates the field is of type
Date.

To pull just the date portion and then just the time portion you could
use the DateValue() and TimeValue() functions. I opened the Immediate
window (Ctrl-G) and typed the text on the lines with question marks and
received the results displayed on the following line:

? datevalue(now())
9/1/2007
? timevalue(now())
11:45:55 PM

If your data field was named "StartTimeStamp" then you might do this in
a SQL query:

SELECT
DateValue([StartTimeStamp]) As StartDay
, TimeValue([StartTimeStamp]) As StartTime
FROM yourTable
.....

This query would not be updateable since StartDay and StartTime are now
*calculated* values, fwiw.

--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft Access MVP
'--------------------------