Re: Problem if Time used as criteria in Access Queries
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Thu, 10 Nov 2005 08:12:16 -0500
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.
"KRISH" <KRISH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B484FD53-1854-44D1-B317-11985D43D364@xxxxxxxxxxxxxxxx
> 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
>
>
> "John Spencer" wrote:
>
>> 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
>>
>>
>> "KRISH" <KRISH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:4539565B-3603-40E1-81D0-0511D0C0A3A7@xxxxxxxxxxxxxxxx
>> > 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
>> >
>> >
>> > "John Spencer" wrote:
>> >
>> >> 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.
>> >>
>> >>
>> >> "KRISH" <KRISH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:82F06F08-0CC1-40AE-954C-A64FD0046D1E@xxxxxxxxxxxxxxxx
>> >> >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
>> >>
>> >>
>> >>
>>
>>
>>
.
- Follow-Ups:
- References:
- Re: Problem if Time used as criteria in Access Queries
- From: John Spencer
- Re: Problem if Time used as criteria in Access Queries
- From: John Spencer
- Re: Problem if Time used as criteria in Access Queries
- Prev by Date: Re: in a query having the between dates and also a sum
- Next by Date: Re: Week number wrong
- Previous by thread: Re: Problem if Time used as criteria in Access Queries
- Next by thread: Re: Problem if Time used as criteria in Access Queries
- Index(es):
Relevant Pages
|
