Querying in VB Code - UTC time

[Tech Geek 1234]

I have posted this under Access Queries, and now feel the Programming group
is more applicable. Below is the basis of my challenge at this point.

The bottom line of what I need is to be able to query a set of records for
the past 30 days, based on UTC time, not "Now()" time. I have a series of
queries that provide me with the totals I need, but they currently all use
the "Now()" function in their calculations.

I am familiar with the "GetSystemTime" function for VB Access (which returns
the date and time in UTC.) But I'm unable to get it and use it in my
queries. (I've tried putting it in a form field and referencing it in place
of the "Now()" in my criteria parameters, but Access gives me "too complex"
errors. e.g.
">=([Forms]![MainForm].[FeasibilityForm].[form].[txtCurrentTime])-30").

Others have pointed me to a code called "GetTimeZoneBias", but I don't see
how that solves this problem as I already have the UTC time - but only in a
form, not the query where I need it.

Is it possible (and feasible) to use code to return the results rather than
a query? I have several queries. One set of queries filters the results by
past 24 hours, 30 days and 90 days. The next set sums the filter queries to
give me a total number. The last query summarizes everything and gives me
the totals of each for a specific person, (e.g. they flew 4 hours in the past
24 hours, 45 hours in the past 30 days, and landed 15 times in the past 90
days). If I can duplicate these queries/results in VB Code, then I should be
able to use UTC time instead of Now(). ????

Any thoughts and suggestions are appreciated. I need to find a way to pull
the records in an accurate time frame, and being off 4-5 hours is not
acceptable in this scenario.

Thanks!

 

[Stefan Hoffmann]

hi,

The bottom line of what I need is to be able to query a set of records for
the past 30 days, based on UTC time, not "Now()" time.

The domain controller can provide NTP time, when setup properly. So
Now() will return the correct time.

I am familiar with the "GetSystemTime" function for VB Access (which returns
the date and time in UTC.) But I'm unable to get it and use it in my
queries. (I've tried putting it in a form field and referencing it in place
of the "Now()" in my criteria parameters, but Access gives me "too complex"
errors. e.g.
">=([Forms]![MainForm].[FeasibilityForm].[form].[txtCurrentTime])-30").

Use a proxy function in default module, e.g:

Option Compare Database
Option Explicit

Public Function GetMyDateTime() As Date

GetMyDateTime = GetSystemTime(...)

End Function

you can then use it in your queries: =GetMyDateTime()

mfG
--> stefan <--

 

[Tech Geek 1234]

That sounds like it might work. However, I have to admit I don't understand
some of the terms you used and where to put this. I understand coding within
a form and using events to trigger the code, beyond that I need some coaching.

You mentioned "domain controller" and "proxy function in default module".
It almost looks like you gave 2 solutions? Unfortunately, I'm not completely
clear on either.

The domain controller sounds simpler; does it only affect the specific
database? I will be putting this db on a network drive for a few people to
share, so it needs to only affect the current database. What exactly is NTP?

As for the coding part, I'm a little confused as to where to put your piece,
and what goes in the arguments for "GetSystemTime(...)". Below is the code
and function I know of now and I call it within the On Current event for my
main form to put the date in an unbound field on my form.

Thanks!

Option Compare Database
Private Declare Sub GetSystemTime Lib "kernel32" _
(lpSystemTime As SYSTEMTIME)

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

Function NowUTC() As Date

Dim st As SYSTEMTIME

GetSystemTime st

With st
NowUTC = _
DateSerial(.wYear, .wMonth, .wDay) + _
TimeSerial(.wHour, .wMinute, .wSecond)
End With

End Function

hi,

The bottom line of what I need is to be able to query a set of records for
the past 30 days, based on UTC time, not "Now()" time.

The domain controller can provide NTP time, when setup properly. So
Now() will return the correct time.

I am familiar with the "GetSystemTime" function for VB Access (which returns
the date and time in UTC.) But I'm unable to get it and use it in my
queries. (I've tried putting it in a form field and referencing it in place
of the "Now()" in my criteria parameters, but Access gives me "too complex"
errors. e.g.
">=([Forms]![MainForm].[FeasibilityForm].[form].[txtCurrentTime])-30").

Use a proxy function in default module, e.g:

Option Compare Database
Option Explicit

Public Function GetMyDateTime() As Date

GetMyDateTime = GetSystemTime(...)

End Function

you can then use it in your queries: =GetMyDateTime()

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

That sounds like it might work. However, I have to admit I don't understand
some of the terms you used and where to put this.

Tech Geek? You should choose your nick with more respect to your abilities.

You mentioned "domain controller" and "proxy function in default module".
It almost looks like you gave 2 solutions? Unfortunately, I'm not completely
clear on either.

Yes.

The first one is an administrative job.

When your client PC is run in a domain using an Active Directory, the
domain should always have setup the time service, which uses NTP
(Network Time Protocol) to synchronize it with a time normal (an atom
clock, e.g. ntp1.ptb.de offers stratum-1 time).

When using a standalone XP you can setup the time service to do the same
job.

If done so, Now() returns a date/time as precise as possible.

The second one is based on your working method to get an appropriate
date/time value.

What exactly is NTP?

Go ask Wikipedia.

As for the coding part, I'm a little confused as to where to put your piece,
and what goes in the arguments for "GetSystemTime(...)". Below is the code
and function I know of now and I call it within the On Current event for my
main form to put the date in an unbound field on my form.

That's what i had in mind. I assume your code is stored in a normal module.

Option Compare Database

Here is an

Option Explicit

missing.

Private Declare Sub GetSystemTime Lib "kernel32" _
(lpSystemTime As SYSTEMTIME)

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

Function NowUTC() As Date

Declare it as

Public Function NowUTC() As Date

Dim st As SYSTEMTIME

GetSystemTime st

With st
NowUTC = _
DateSerial(.wYear, .wMonth, .wDay) + _
TimeSerial(.wHour, .wMinute, .wSecond)
End With

End Function

You can use this function in any expression. This includes in a query:

SELECT * FROM Table WHERE DateValue <= NowUTC()


mfG
--> stefan <--