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