Convert to PST from GMT time

[PhilT via AccessMonster.com]

I need help to convert date and time to value rather than string. This is the
background.

1. A table contain a Date field and Time field separetely.
2. In query, I combine Date and Time into one because if I subtract 8 hours
from GMT time, I could have both date and time correctly. Quick and easy.

Problems:
1. When I combine Date and Time, I try Format() which convert to string - I
don't want this because it cannot calculate.
2. I try Datevalue and Timevalue separately such as datevalue([date]) & " " &
timevalue([time]), but no good.

Can someone help me? Thanks.

 

[fredg]

I need help to convert date and time to value rather than string. This is the
background.

1. A table contain a Date field and Time field separetely.
2. In query, I combine Date and Time into one because if I subtract 8 hours
from GMT time, I could have both date and time correctly. Quick and easy.

Problems:
1. When I combine Date and Time, I try Format() which convert to string - I
don't want this because it cannot calculate.
2. I try Datevalue and Timevalue separately such as datevalue([date]) & " " &
timevalue([time]), but no good.

Can someone help me? Thanks.

An understanding of how Access stores a Date and Time datatype field
might help.

If you wish to combine the date and the time fields just add them:
Combined:[DateField] + [TimeField]
Note that there is no need to Format the values before combining them.
If you need to later "display" the combined value (in a form or
report) you can do so, but then the displayed value is text:
=Format([Combined],"mm/dd/yyyy hh:nn:ss am/pm")

A Date datatype field actually stores the date as a number (the count
of the number of days elapsed since 12/30/1899).
Today's date is stored as 40141.0

A Time value is stored as the fractional part of a day. So 6:00 PM is
stored as 0.75 (or 3/4 of one 24 hour day).

If you add the 2 values together you would get 40141.75
which, as a DateTime value, is 11/24/2009 6:00 PM.

So why do you have 2 separate fields for the date and time?
Why not just one field of date and time?

 

[PhilT via AccessMonster.com]

I need help to convert date and time to value rather than string. This is the
background.

[quoted text clipped - 10 lines]

Can someone help me? Thanks.

An understanding of how Access stores a Date and Time datatype field
might help.

If you wish to combine the date and the time fields just add them:
Combined:[DateField] + [TimeField]
Note that there is no need to Format the values before combining them.
If you need to later "display" the combined value (in a form or
report) you can do so, but then the displayed value is text:
=Format([Combined],"mm/dd/yyyy hh:nn:ss am/pm")

A Date datatype field actually stores the date as a number (the count
of the number of days elapsed since 12/30/1899).
Today's date is stored as 40141.0

A Time value is stored as the fractional part of a day. So 6:00 PM is
stored as 0.75 (or 3/4 of one 24 hour day).

If you add the 2 values together you would get 40141.75
which, as a DateTime value, is 11/24/2009 6:00 PM.

So why do you have 2 separate fields for the date and time?
Why not just one field of date and time?

Fredg,

Very appreciate for your support. I use the [date] + [time] function to
solved my problem. However, you don't want to know why I have two separate
date and time field. It went back to 4 years ago, when I created this
database. The Unix systems were straight forward, and I don't have much of
experience with Access. Now, some of Unix systems are in GMT and other are in
local time. I try generate report for user login outside the normal working
hours, and GMT is not easy.