UTC and Now() function

[techgeek1234]

I am familiar with the "GetSystemTime" function for VB Access. However, I
need to use UTC for a query parameter. I have a database in which all date
and time fields will be entered in UTC time. I need a query to give me the
past 30 days of flights. I am currently using "Now()-30" to give me this
information. However, it's off by 5 hours because I'm in EST zone. I need
to find a way to use UTC/Greenwich within the query. This doesn't seem
possible, so I tried retrieving the time I need from a form rather than using
the Now() function.

I've tried creating a txtCurrentTime field in a form, which will populate
with NowUTC time upon the Current event. Then I tried referencing that field
in the query parameter, replacing "Now()-30" with ">=([Forms]![MainForm].
[FeasibilityForm].[form].[txtCurrentTime])-30". It does not work - it gives
me a "too complex" error or run time error on other fields from the query.

Any thoughts 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!

 

[John Nurick]

There's sample code here
http://vbnet.mvps.org/code/locale/gettimezonebias.htm that uses API
calls to get information about the current time zone.

(Despite the site's name, it's classic Visual Basic code that should
need little or no modification to work in VBA.)

I am familiar with the "GetSystemTime" function for VB Access. However, I
need to use UTC for a query parameter. I have a database in which all date
and time fields will be entered in UTC time. I need a query to give me the
past 30 days of flights. I am currently using "Now()-30" to give me this
information. However, it's off by 5 hours because I'm in EST zone. I need
to find a way to use UTC/Greenwich within the query. This doesn't seem
possible, so I tried retrieving the time I need from a form rather than using
the Now() function.

I've tried creating a txtCurrentTime field in a form, which will populate
with NowUTC time upon the Current event. Then I tried referencing that field
in the query parameter, replacing "Now()-30" with ">=([Forms]![MainForm].
[FeasibilityForm].[form].[txtCurrentTime])-30". It does not work - it gives
me a "too complex" error or run time error on other fields from the query.

Any thoughts 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!

 

[techgeek1234 via AccessMonster.com]

I am able to get the UTC time using a similar code. ("GetSystemTime" returns
the UTC value, not the local value.) My challenge at this point is using
that specific time in a query. I can't use the "Now()" function because it
gives me local time, and I need to run my calculation on UTC time instead.

I can't figure out how to do that. I've tried replacing the "Now()" in my
query with the field from the form that contains the UTC time, but Access
gives me a "too complex" error. I don't know how else to figure the past 30
days from UTC time rather than local time.

Is there a relatively simple way 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 other suggestions are appreciated!

There's sample code here
http://vbnet.mvps.org/code/locale/gettimezonebias.htm that uses API
calls to get information about the current time zone.

(Despite the site's name, it's classic Visual Basic code that should
need little or no modification to work in VBA.)

I am familiar with the "GetSystemTime" function for VB Access. However, I
need to use UTC for a query parameter. I have a database in which all date

[quoted text clipped - 16 lines]

Thanks!

 

[John Nurick]

I've never had to do any of this myself. But what I had in mind is that
GetCurrentTimeBias() gives you the difference between system time and
UTC, so you can get system time with Now(), subtract the bias, and use
the result in your query.

I am able to get the UTC time using a similar code. ("GetSystemTime" returns
the UTC value, not the local value.) My challenge at this point is using
that specific time in a query. I can't use the "Now()" function because it
gives me local time, and I need to run my calculation on UTC time instead.

I can't figure out how to do that. I've tried replacing the "Now()" in my
query with the field from the form that contains the UTC time, but Access
gives me a "too complex" error. I don't know how else to figure the past 30
days from UTC time rather than local time.

Is there a relatively simple way 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 other suggestions are appreciated!

There's sample code here
http://vbnet.mvps.org/code/locale/gettimezonebias.htm that uses API
calls to get information about the current time zone.

(Despite the site's name, it's classic Visual Basic code that should
need little or no modification to work in VBA.)

I am familiar with the "GetSystemTime" function for VB Access. However, I
need to use UTC for a query parameter. I have a database in which all date

[quoted text clipped - 16 lines]

Thanks!

 

[Tech Geek 1234]

Ah, that makes sense. I'll give that a try. I was able to get it working
with a static number (+5 for EST) so maybe I can get it working with this so
I don't have to worry about Daylight Savings time.

Thanks!

I've never had to do any of this myself. But what I had in mind is that
GetCurrentTimeBias() gives you the difference between system time and
UTC, so you can get system time with Now(), subtract the bias, and use
the result in your query.

I am able to get the UTC time using a similar code. ("GetSystemTime" returns
the UTC value, not the local value.) My challenge at this point is using
that specific time in a query. I can't use the "Now()" function because it
gives me local time, and I need to run my calculation on UTC time instead.

I can't figure out how to do that. I've tried replacing the "Now()" in my
query with the field from the form that contains the UTC time, but Access
gives me a "too complex" error. I don't know how else to figure the past 30
days from UTC time rather than local time.

Is there a relatively simple way 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 other suggestions are appreciated!

There's sample code here
http://vbnet.mvps.org/code/locale/gettimezonebias.htm that uses API
calls to get information about the current time zone.

(Despite the site's name, it's classic Visual Basic code that should
need little or no modification to work in VBA.)


I am familiar with the "GetSystemTime" function for VB Access. However, I
need to use UTC for a query parameter. I have a database in which all date
[quoted text clipped - 16 lines]

Thanks!

 

[John Nurick]

BTW, if you're using Windows 2000 or earlier, your system may not know
about recent changes to daylight savings dates in various jurisdictions
including the USA and Canada. See e.g.
http://articles.techrepublic.com.com/2100-1009_11-6159840.html

Ah, that makes sense. I'll give that a try. I was able to get it working
with a static number (+5 for EST) so maybe I can get it working with this so
I don't have to worry about Daylight Savings time.

Thanks!

I've never had to do any of this myself. But what I had in mind is that
GetCurrentTimeBias() gives you the difference between system time and
UTC, so you can get system time with Now(), subtract the bias, and use
the result in your query.

I am able to get the UTC time using a similar code. ("GetSystemTime" returns
the UTC value, not the local value.) My challenge at this point is using
that specific time in a query. I can't use the "Now()" function because it
gives me local time, and I need to run my calculation on UTC time instead.

I can't figure out how to do that. I've tried replacing the "Now()" in my
query with the field from the form that contains the UTC time, but Access
gives me a "too complex" error. I don't know how else to figure the past 30
days from UTC time rather than local time.

Is there a relatively simple way 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 other suggestions are appreciated!

John Nurick wrote:
There's sample code here
http://vbnet.mvps.org/code/locale/gettimezonebias.htm that uses API
calls to get information about the current time zone.

(Despite the site's name, it's classic Visual Basic code that should
need little or no modification to work in VBA.)


I am familiar with the "GetSystemTime" function for VB Access. However, I
need to use UTC for a query parameter. I have a database in which all date
[quoted text clipped - 16 lines]

Thanks!