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  

UTC and Now() function



 
 
Thread Tools Display Modes
  #1  
Old February 13th, 2007, 07:07 PM posted to microsoft.public.access.queries
techgeek1234
external usenet poster
 
Posts: 1
Default UTC and Now() function

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!

  #2  
Old February 13th, 2007, 11:07 PM posted to microsoft.public.access.queries
John Nurick
external usenet poster
 
Posts: 492
Default UTC and Now() function

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.)

On Tue, 13 Feb 2007 18:07:36 GMT, "techgeek1234" u31724@uwe wrote:

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 [Microsoft Access MVP]

Please respond in the newsgroup and not by email.
  #3  
Old February 15th, 2007, 03:16 PM posted to microsoft.public.access.queries
techgeek1234 via AccessMonster.com
external usenet poster
 
Posts: 1
Default UTC and Now() function

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!


--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.


--
Message posted via http://www.accessmonster.com

  #4  
Old February 16th, 2007, 08:21 AM posted to microsoft.public.access.queries
John Nurick
external usenet poster
 
Posts: 492
Default UTC and Now() function

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.



On Thu, 15 Feb 2007 14:16:02 GMT, "techgeek1234 via AccessMonster.com"
u31724@uwe wrote:

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!


--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.


--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.
  #5  
Old February 16th, 2007, 03:04 PM posted to microsoft.public.access.queries
Tech Geek 1234
external usenet poster
 
Posts: 13
Default UTC and Now() function

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!

"John Nurick" wrote:

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.



On Thu, 15 Feb 2007 14:16:02 GMT, "techgeek1234 via AccessMonster.com"
u31724@uwe wrote:

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!

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.


--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

  #6  
Old February 16th, 2007, 09:09 PM posted to microsoft.public.access.queries
John Nurick
external usenet poster
 
Posts: 492
Default UTC and Now() function

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...1-6159840.html

On Fri, 16 Feb 2007 06:04:07 -0800, Tech Geek 1234
wrote:

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!

"John Nurick" wrote:

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.



On Thu, 15 Feb 2007 14:16:02 GMT, "techgeek1234 via AccessMonster.com"
u31724@uwe wrote:

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!

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.


--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.


--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.
 




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 12:20 PM.


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