Re: Problem if Time used as criteria in Access Queries

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"John Spencer" wrote:

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

Relevant Pages

  • Re: seperate fields
    ... ok here is my sql statement ... FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON ... "John Spencer" wrote: ... fielde and set the criteria to 5 the insert SplitPath ...
    (microsoft.public.access.queries)
  • Re: Update query & null values
    ... "John Spencer" wrote: ... The criteria field that I mentioned is when you have the query open in ... It would have helped if you had shown us the sql statement. ... Please reply to the newsgroup. ...
    (microsoft.public.access.queries)
  • Re: Syntax error in FROM clause
    ... You would also need to set command type to adCmdText ... > But I didn't specify an SQL statement. ... > And I choose the Table record source. ...
    (microsoft.public.vb.database.ado)
  • Re: IF STATEMENT
    ... In a SQL statement that would look like ... John Spencer ... Access MVP 2002-2005, 2007 ... don't have a field named myField. ...
    (microsoft.public.access.gettingstarted)
  • Re: SQL Index Problem
    ... An SQL-index cannot be specified in an SQL statement. ... specify it in the SQL statement. ... The query optimizer does not care about any access paths stored in an ... DDS described logical file. ...
    (comp.sys.ibm.as400.misc)