Queries with date/time format prob

  • Thread starter Thread starter andrea kampa
  • Start date Start date
A

andrea kampa

A simple question. I have Access 2000 and I've created a table with two
date/time fields. In these fields I'd like to store the hour of the
beginning of the connection with my site. In the other field, I'd store the
hour of the Log off.
My favourite format is 24h. Then I've created a query to calculate the log
time:
gross_worktime: [logoff]-[login]
eg: gross_worktime=18.45 - 8.30 then the result shoud be 10.15 about.
On the contrary the query's result is: 0,427083333333333
Why does it happen?
I've checked the query format properties but the date/time format is not
concerned.
Please, have you suggestions or troubleshootings to tell access to give me
my result?
Or is it not possible to make date/time calculations with Access 2000?

Andrea Campa
 
Try Format(0.427083333333333, "hh:nn") (replace h and n if the abbreviations
for hour and minute are different in Italian)

Date/Times are stored as 8 byte floating point numbers, where the integer
part represents the date as the number of days relative to 30 Dec. 1899, and
the decimal part represents the time as a fraction of a day. Recognize,
though, that date/time fields are intended for timestamps, not for
durations. If all you've got is a time, Access will accept it, but treats it
as that time on 30 Dec, 1899. If you add times together and they exceed 24
hours, Access will only return the amount that's over 24 (or 48, or 72 or
whatever) as a time, and will return the remainder as a day.
 
Your suggestion is right. Thanks a million!

Andrea Campa

Douglas J. Steele said:
Try Format(0.427083333333333, "hh:nn") (replace h and n if the abbreviations
for hour and minute are different in Italian)

Date/Times are stored as 8 byte floating point numbers, where the integer
part represents the date as the number of days relative to 30 Dec. 1899, and
the decimal part represents the time as a fraction of a day. Recognize,
though, that date/time fields are intended for timestamps, not for
durations. If all you've got is a time, Access will accept it, but treats it
as that time on 30 Dec, 1899. If you add times together and they exceed 24
hours, Access will only return the amount that's over 24 (or 48, or 72 or
whatever) as a time, and will return the remainder as a day.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



andrea kampa said:
A simple question. I have Access 2000 and I've created a table with two
date/time fields. In these fields I'd like to store the hour of the
beginning of the connection with my site. In the other field, I'd store the
hour of the Log off.
My favourite format is 24h. Then I've created a query to calculate the log
time:
gross_worktime: [logoff]-[login]
eg: gross_worktime=18.45 - 8.30 then the result shoud be 10.15 about.
On the contrary the query's result is: 0,427083333333333
Why does it happen?
I've checked the query format properties but the date/time format is not
concerned.
Please, have you suggestions or troubleshootings to tell access to give me
my result?
Or is it not possible to make date/time calculations with Access 2000?

Andrea Campa
Click to expand...
Click to expand...
 
Back
Top