A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Convert to PST from GMT time



 
 
Thread Tools Display Modes
  #1  
Old November 24th, 2009, 09:14 PM posted to microsoft.public.access.queries
PhilT via AccessMonster.com
external usenet poster
 
Posts: 22
Default Convert to PST from GMT time

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

  #2  
Old November 24th, 2009, 10:21 PM posted to microsoft.public.access.queries
fredg
external usenet poster
 
Posts: 4,386
Default Convert to PST from GMT time

On Tue, 24 Nov 2009 20:14:07 GMT, PhilT via AccessMonster.com wrote:

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?

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
  #3  
Old November 25th, 2009, 12:20 AM posted to microsoft.public.access.queries
PhilT via AccessMonster.com
external usenet poster
 
Posts: 22
Default Convert to PST from GMT time

fredg wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.