Wow. This is great. Thanks Graham.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, June 05, 2014 11:50 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] now() with time zone adjustment
Hi Liz
The computer already stores its time as UTC. I suggest you use API calls to get the system time and convert it to date/time format, and store that instead of Now().
Try the code below – not sure how well it will copy and paste from the forum L
It includes a function to get the current UTC time, which you can use instead of Now(), and some functions to convert both ways between UTC and local time. You don’t need to worry about the time zone because the system takes care of that.
============ Start code =================
Option Compare Text
Option Explicit
Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type
Private Type TIME_ZONE_INFORMATION
Bias As Long
'StandardName As String * 64
StandardName(0 To 31) As Integer
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName(0 To 31) As Integer
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type
Private Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
Private Declare Function SystemTimeToTzSpecificLocalTime _
Lib "kernel32" ( _
lpTimeZoneInformation As TIME_ZONE_INFORMATION, _
lpUniversalTime As SYSTEMTIME, _
lpLocalTime As SYSTEMTIME _
) As Long
Private Declare Function TzSpecificLocalTimeToSystemTime _
Lib "kernel32" ( _
lpTimeZoneInformation As TIME_ZONE_INFORMATION, _
lpLocalTime As SYSTEMTIME, _
lpUniversalTime As SYSTEMTIME _
) As Long
Private Declare Function GetTimeZoneInformation _
Lib "kernel32" ( _
lpTimeZoneInformation As TIME_ZONE_INFORMATION _
) As Long
Private Function SystemTimeToDateTime(t As SYSTEMTIME) As Date
Const cMsPerDay = 86400000#
SystemTimeToDateTime = DateSerial(t.wYear, t.wMonth, t.wDay) _
+ TimeSerial(t.wHour, t.wMinute, t.wSecond) _
+ (t.wMilliseconds / cMsPerDay)
End Function
Private Function DateTimeToSystemTime(tDate As Date) As SYSTEMTIME
With DateTimeToSystemTime
.wYear = YEAR(tDate)
.wMonth = Month(tDate)
.wDay = Day(tDate)
.wHour = Hour(tDate)
.wMinute = Minute(tDate)
.wSecond = Second(tDate)
.wMilliseconds = 0
End With
End Function
' Convert UTC date/time to the local time zone
Public Function UTCtoLocal(ByVal tDate As Date) As Date
Dim tzi As TIME_ZONE_INFORMATION
Dim stUTC As SYSTEMTIME
Dim stLocal As SYSTEMTIME
Dim lRes As Long
lRes = GetTimeZoneInformation(tzi)
stUTC = DateTimeToSystemTime(tDate)
lRes = SystemTimeToTzSpecificLocalTime(tzi, stUTC, stLocal)
UTCtoLocal = SystemTimeToDateTime(stLocal)
End Function
' Convert local date/time to UTC
Public Function LocalToUTC(ByVal dtLocal As Date) As Date
Const cMinsPerDay = 1440
Dim tzi As TIME_ZONE_INFORMATION
Dim stUTC As SYSTEMTIME
Dim stLocal As SYSTEMTIME
Dim dtUTC As Date
Dim lRes As Long
lRes = GetTimeZoneInformation(tzi)
stLocal = DateTimeToSystemTime(dtLocal)
lRes = TzSpecificLocalTimeToSystemTime(tzi, stLocal, stUTC)
LocalToUTC = SystemTimeToDateTime(stUTC)
End Function
' Return current time as UTC
Public Function UTCTime() As Date
Dim t As SYSTEMTIME
GetSystemTime t
UTCTime = SystemTimeToDateTime(t)
End Function
' Return the exact local time
' - like Now() but includes milliseconds
Public Function LocalTime() As Date
Dim t As SYSTEMTIME
GetLocalTime t
LocalTime = SystemTimeToDateTime(t)
End Function
================ End Code =============
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, 6 June 2014 01:07
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] now() with time zone adjustment
Pros, I have a database used in 5 different countries, India being one of them. I capture log in time and log out time as well as other activities with now(). Is there a way I can adjust the time because it is fouling up my stats. I just got informed I have 30 more users in India, so it is getting to be more important.
Respectfully,
Liz Ravenwood
Programmer / Analyst
B/E Aerospace | Super First Class Environments
1851 S Pantano Road | Tucson, Arizona 85710
Office +1.520.239.4808 |
beaerospace.com
Passion to Innovate. Power to Deliver
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Posted by: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (15) |
Tidak ada komentar:
Posting Komentar