Date/Time in Number Format

[Guest]

Thanks in advance.

A query named Qry_Time_Table has fields such as Date, Start-Time...
But these are numbers representing dates and times.
Below are examples.
(Ex) Date Start_Time
38889 56001690

(What I want to achieve)
Date Start_Time
6/21/06 6/21/06 09:30:00

How can I change these numbers into Date/Time format in calculated fields of
the query or vice versa?

 

[John Vinson]

Thanks in advance.

A query named Qry_Time_Table has fields such as Date, Start-Time...
But these are numbers representing dates and times.
Below are examples.
(Ex) Date Start_Time
38889 56001690

(What I want to achieve)
Date Start_Time
6/21/06 6/21/06 09:30:00

How can I change these numbers into Date/Time format in calculated fields of
the query or vice versa?

Is this an Access table, or some external table? What are the
datatypes of these two fields?

Access Date/Time values are stored as Double Float numbers, a count of
days and fractions of a day since midnight, December 30, 1899. 6/21/06
is in fact 38889, as you can determine using the CDate() function:

?cdate(38889)
6/21/2006

but I can't see the logic of your Start_time value:

?cdbl(#6/21/06 09:30:00#)
38889.3958333333

Aha... maybe it's a long integer count of minutes:

?DateAdd("n", -56001690, #6/21/06 09:30:00#)
12/29/1899 8:00:00 AM

though what the 16 hours differance means is beyond me!

John W. Vinson[MVP]

 

[strive4peace]

Hi Jay,

turn on the display of the Properties window

(right-click anywhere and choose "Properties" from the
shortcut menu)

click in your column name

set the Format property -- m/d/yy hh:nn:ss
and press ENTER

this will show you a time based on a 24-hour clock

when you say this is what you want to archive... a format
is a way of looking at something; it does not change what is
stored -- you can change the format property of your
archive table too.

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*