 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005Problem if Time used as criteria in Access Queries
I want to use Time as criteria to Acess queries. Problem is if the data in my table contains hrs not minutes (10:00 AM) then my query is working fine. But the data contains time say 10:30 AM/PM & if i use 10:30Am/Pm as criteria, then my query is not retriving any records contain. Please help how to solve this problem. sai Is the field a DateTime field or is it a text field? Is the field storing only a time? What happens if you try to enter a range instead of a single time? For example: Field: SomeTimeField Criteria: Between #10:29:59 AM# and #10:30:01 AM# DateTime fields are stored as numbers (Type Double) and in some instances there can be small rounding errors which could possibly cause the time value to be slightly different than the expected value. >I want to use Time as criteria to Acess queries. Problem is if the data in >my [quoted text clipped - 6 lines] > > sai Dear John, Thanks for your reply. I am using datatype-Date/Time and format-Medium Time. Please help how to solve the problem. Thanks. krish > Is the field a DateTime field or is it a text field? Is the field storing > only a time? [quoted text clipped - 19 lines] > > > > sai Format controls how the data is displayed. It has nothing to do with how the data is stored and therefore with how you search for the data. Did you try my suggestion of using a range of times? If so what result did you get? I asked if you were storing only the time. Is that the case? Please post your SQL statement. (Possibly unneeded instructions follow) Open the query Select View:Sql from the Menu Select all the text Copy it Paste it into the message > Dear John, > Thanks for your reply. [quoted text clipped - 31 lines] >> > >> > sai Dear John, Thanks for responding my email. Please find the SQL statement you asked. Case (1): Code retriving No results: SELECT doc_vlist.drid, doc_vlist.vday, doc_vlist.vtime FROM doc_vlist WHERE (((doc_vlist.vtime)=#12/30/1899 10:0:0#)); Case (2): Code working fine: SELECT doc_vlist.drid, doc_vlist.vday, doc_vlist.vtime FROM doc_vlist WHERE (((doc_vlist.vtime)=#12/30/1899 10:30:0#)); Please note in Case (2) the time criteria contains Minutes. One more thing what is the problem with the following code: SELECT drid,vday,vtime FROM doc_vlist WHERE vtime=#10:00 PM#; Is it necessary to give '12/30/1899' ? Its automatically taken by MS Access. Hope the above will help you to find the solution. Thanks for your help. Krish > Format controls how the data is displayed. It has nothing to do with how > the data is stored and therefore with how you search for the data. [quoted text clipped - 48 lines] > >> > > >> > sai Try the following variants 1) Specify the AM or PM. If you don't, I believe that Access will default to a 24 hour clock. WHERE doc_vlist.vtime = #12/30/1899 10:00:00 AM# 2) Try for a range WHERE doc_vlist.vtime BETWEEN #12/30/1899 9:59:59 AM# AND #12/30/1899 10:00:01 AM# 3) Make sure that there is no date attached to the entry. WHERE TimeValue(doc_vList.vTime = #10:00:00 AM# 4) WHERE TimeValue(doc_vlist.vtime) BETWEEN #9:59:59 AM# AND #10:00:01 AM# Specifying the date component (zero date is 12/30/1899 should not be needed. > Dear John, > Thanks for responding my email. Please find the SQL statement you asked. [quoted text clipped - 82 lines] >> >> > >> >> > sai Dear John, Thanks. if i use BETWEEN #12/30/1899 9:59:59 AM# AND #12/30/1899 10:00:01 AM#. Its working fine. But why the access is not accepting #10:00:00 AM#. If I have to use between clause. Then in case of any scientific data, where i have to consider seconds as criteria. I can not use Access. When Access accepting #10:30:00# then why not #10:00:00#. If you got right solution for the problem. Please let me know. Thanks once again. sai > Try the following variants > 1) Specify the AM or PM. If you don't, I believe that Access will default [quoted text clipped - 99 lines] > >> >> > > >> >> > sai Well, you can make it a bit more accurate by using greater than and less than as in WHERE vTime > #12/30/1899 9:59:59 AM# AND vTime < #12/30/1899 10:00:01 The problem is that datetime is stored as a double number and the fractional portion of that is subject to inaccuracy since it cannot always be stored accurately - just as 1/3 cannot be represented accurately in a base 10 system. If you only need time, you might consider storing the time in a long number field or currency field as the number of seconds since midnight. You would have to write some simple functions to get the number of seconds to store and the time to return. Something like: DateDiff("s",0,#10:00:00 AM#) --> 36000 DateAdd("s",36000,0) --> 10:00:00 AM > Dear John, > Thanks. if i use BETWEEN #12/30/1899 9:59:59 AM# AND #12/30/1899 10:00:01 [quoted text clipped - 123 lines] >> >> >> > >> >> >> > sai |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.