Convert Number (Double) to Time

My job has presented me with an old database where one of
the things I don't like is the way the time of day has been
setup and entered. It is a number data type, double
precision, and has entries like: "0945", "1005".

Using Access 2000, when I simply change the data type to
"Date/Time", the results are not good (all values change to
"0:00". I did find, however, that if I reset the format of
the field to "@@:@@", the proper (desired?) time values
would suddenly appear (Access would actually change the
format setting to "@@\:@@"). I can't seem to find any
reference to the backslash character in the help files. If
I change the format to "general date", then I'm back to
"0:00" values. I would have expected this format setting to
give me a date and time, like: "0/0/00 09:45", or something
along those lines.

My question now is, do I have a truly valid "time" field?
And what is that backslash in the format setting?

I am thinking about putting date and time as one field, but
I think I first need to get the time field straightened out
(the date field is already Date/Time format.

And then of course, try to convince the non-db types at work
(all the others) that it's *good* to use the date/time data
type for dates and times (and *not* name the fields "DATE"
and "TIME").
--
Thanks,tbl

Access has a Date/Time data type. If you only stuff a time-value into this
kind of field, you're telling Access that all the times happened on the same
day (some arbitrary date back in the late 1800's).

If you actually have date AND time values, you can store these in the
Date/Time field. Otherwise, maybe you need to store the time-values as
text, and work out the time-arithmetic procedures you'll need (but if they
are date/time values, Access has ready-made functions for those).

Regards

Jeff Boyce
Microsoft Office/Access MVP


"tbl" wrote in message
...
My job has presented me with an old database where one of
the things I don't like is the way the time of day has been
setup and entered. It is a number data type, double
precision, and has entries like: "0945", "1005".

Using Access 2000, when I simply change the data type to
"Date/Time", the results are not good (all values change to
"0:00". I did find, however, that if I reset the format of
the field to "@@:@@", the proper (desired?) time values
would suddenly appear (Access would actually change the
format setting to "@@\:@@"). I can't seem to find any
reference to the backslash character in the help files. If
I change the format to "general date", then I'm back to
"0:00" values. I would have expected this format setting to
give me a date and time, like: "0/0/00 09:45", or something
along those lines.

My question now is, do I have a truly valid "time" field?
And what is that backslash in the format setting?

I am thinking about putting date and time as one field, but
I think I first need to get the time field straightened out
(the date field is already Date/Time format.

And then of course, try to convince the non-db types at work
(all the others) that it's *good* to use the date/time data
type for dates and times (and *not* name the fields "DATE"
and "TIME").
--
Thanks,tbl

tbl,

The clue you are missing is that in a Date/Time table field, the date
is stored as an integer and the time as a fraction. In the pseudo-time
field you inherited, the fractional part is zero. So when you tell
Access it's a date/time and format it as time only, you get 0:00.

Your @@\:@@ is simply formatting the integer with a colon inserted.
That isn't telling Access that it's a time, just how to format a
number.

Fixing your database is easy. Set up a query, use the table containing
the date and time, change the type to update, use the date field. In
the Update To line put

[olddate]+TimeSerial([oldtime]\100,[oldtime] Mod 100,0)

Note the use of a backslash for "divide and truncate", which is
important.

Your "olddate" field now contains the date and time. Delete the old
field when you are ready. Don't run the update query more than once
unless you clear the time part of the date/time field first!

Edward

On 23 Jun 2006 18:37:55 -0700, "Edward Reid"
wrote:

tbl,

The clue you are missing is that in a Date/Time table field, the date
is stored as an integer and the time as a fraction. In the pseudo-time
field you inherited, the fractional part is zero. So when you tell
Access it's a date/time and format it as time only, you get 0:00.

Your @@\:@@ is simply formatting the integer with a colon inserted.
That isn't telling Access that it's a time, just how to format a
number.

Fixing your database is easy. Set up a query, use the table containing
the date and time, change the type to update, use the date field. In
the Update To line put

[olddate]+TimeSerial([oldtime]\100,[oldtime] Mod 100,0)

Note the use of a backslash for "divide and truncate", which is
important.

Your "olddate" field now contains the date and time. Delete the old
field when you are ready. Don't run the update query more than once
unless you clear the time part of the date/time field first!


Thank you Edward! Very informative post. Off to the lab...
--
tbl

On 23 Jun 2006 18:37:55 -0700, "Edward Reid"
wrote:

tbl,

The clue you are missing is that in a Date/Time table field, the date
is stored as an integer and the time as a fraction. In the pseudo-time
field you inherited, the fractional part is zero. So when you tell
Access it's a date/time and format it as time only, you get 0:00.

Your @@\:@@ is simply formatting the integer with a colon inserted.
That isn't telling Access that it's a time, just how to format a
number.

Fixing your database is easy. Set up a query, use the table containing
the date and time, change the type to update, use the date field. In
the Update To line put

[olddate]+TimeSerial([oldtime]\100,[oldtime] Mod 100,0)

Note the use of a backslash for "divide and truncate", which is
important.

Your "olddate" field now contains the date and time. Delete the old
field when you are ready. Don't run the update query more than once
unless you clear the time part of the date/time field first!


That was certainly a winner, Edward.

But it has led me to a new apprehension about my new,
combined date and time field.

Some of the records that I need to import already have a
valid date type field, but no time was entered by the data
entry person, so I guess the system time was silently put in
there(?), with no one the wiser--until now.

It's easy enough for me to gather these records up by query,
but I'm not able to sort out how to eliminate the time info
(or zero it out).

Is it possible to get these records to contain just the date
portion?
--
Thanks,tbl

Tbl,

If you have a Date/Time data type field, which contains date and time
information, and you only want the date portion, you can use an Update
Query to update YourDateField to...
Int([YourDateField])
or...
DateValue([YourDateField])
either will work.

--
Steve Schapel, Microsoft Access MVP

tbl wrote:

Some of the records that I need to import already have a
valid date type field, but no time was entered by the data
entry person, so I guess the system time was silently put in
there(?), with no one the wiser--until now.

It's easy enough for me to gather these records up by query,
but I'm not able to sort out how to eliminate the time info
(or zero it out).

Is it possible to get these records to contain just the date
portion?

On Sun, 25 Jun 2006 21:36:36 +1200, Steve Schapel
wrote:

Tbl,

If you have a Date/Time data type field, which contains date and time
information, and you only want the date portion, you can use an Update
Query to update YourDateField to...
Int([YourDateField])
or...
DateValue([YourDateField])
either will work.


Thank you Steve!
--
tbl