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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|