Date AND time as Long Integer

[Guest]

I'm sure there must be an easy answer to this but must admit I can't find it.
I have a table which stores 6 pairs of 2 =now functions (from input form) so
that I can work out the elapsed time between each pair. I can't use DateDiff
and I need to include the results in an equation.

Sample 03/05/2006 11:41:15

I know that I need to store that information as a long integer so that I can
simply subtract the end time from the start to get the elapsed but when I
open the table I only have the date section (before the decimal point) - the
time section is missing.

How do I store the date and time as a long integer so I can use it in a query.

Any help would be greatly appreciated.

Thank you

 

[Tom Ellison]

Dear Jenny:

A date/time column is capable of storing both date and time. Just because
it is capable does not mean you have used the feature that stores times.

And, just because you don't see any times doesn't mean they aren't there.
The designer may choose any one of several formats for displaying the values
in a date/time column.

In the table design view, there is a property of a date/time column called
"Format". For starters, please use the General Date format shown. You will
then see the time of day. Many of the available formats do not display the
time of day. That is your choice. But, just because you don't see any time
of day, doesn't mean there isn't one.

If you "store the date and time as a long integer" then there will be no
time there, only (perhaps) dates. Store date/time values as the date/time
datatype.

I hope this is the information you needed. Please let me know.

Tom Ellison

 

[Rob Parker]

Hi Jenny,

When you say " I know that I need to store that information as a long
integer ... ", you reveal the cause of your problem!

A Date/Time datatype stores date and time; the date is the integer part (as
days from 30 Dec 18999), and the time is the decimal part (as a proportion
of 24 hours). Therefore, if you store the result of a Now() function into a
long integer field, you lose the time portion - an integer field (long or
normal) by definition has no decimal portion. You could store the result
into a Single or Double number field, but it's much easier to simply use the
pre-defined Date/Time datatype for the field.

When you do this, your DateDiff function will work as you expect.

HTH,

Rob

 

[Guest]

Hi Tom

Thankls for your quicky reply. Basically what I need to do is give the
total time that an employee has spent on shift (which may or may not be over
night)

e.g
Start time End time
Query to show
02/05/2006 11:23:05 03/05/2006 07:38:05 20:15 hrs worked
05/05/2006 13:10:00 05/05/2006 13:40:00 00:30 hrs worked
08/05/2006 20:07:12 09/05/2006 06:15:23 10:08 hrs worked

Total worked 30:52

I have done this manually but I'm sure that if I could convert the date/time
to a number in a query and take one from the other I would be able to do this.

 

[Guest]

Oh - got it now.

Many many thank Rob for that information, NOW I SEE. Sorry but couldn't
find that bit if info anywhere but it's beenm explained it all makes sense.

Thanks to you both Rob and Tom

Jenny

 

[Marshall Barton]

I'm sure there must be an easy answer to this but must admit I can't find it.
I have a table which stores 6 pairs of 2 =now functions (from input form) so
that I can work out the elapsed time between each pair. I can't use DateDiff
and I need to include the results in an equation.

Sample 03/05/2006 11:41:15

I know that I need to store that information as a long integer so that I can
simply subtract the end time from the start to get the elapsed but when I
open the table I only have the date section (before the decimal point) - the
time section is missing.

How do I store the date and time as a long integer so I can use it in a query.

Elapsed times are **not** date/time values. The integer you
want(?) to store is the number of minutes (or seconds) in
the difference or the two date/time values. If you want to
see hous:minutes then that is a formatting issue, not a
conversion issue. For example,

Minutes = DateDiff("n", start, end)

then a text box expression to display that as hours:minutes
would be:

=minutes \ 60 & ":" & Format(minutes Mod 60, "00")