Problem if Time used as criteria in Access Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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 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
 
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. 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
 
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
 
Back
Top