Re: time criteria in query

I'm sorry....I don't.

I mean no offense but this feels like a
"yada-yada" discussion which might
not mean anything to you if you never
watched Seinfeld, nor participated in
trying to help someone, only to find out
on the thirty-something reply that the
poster has not told the whole story
and the previous 29 "head-against-the-wall"
attempts were never going to come to
fruition because pertinent fact(s) were
not revealed.

Prove me wrong by providing the SQL
with expression that works, then the complete
WHERE clause only for that same query
that errors out.

Or...start a query with just the date field
and an expression,

Expr1: IsDate(Format([DateField],"hh:mm:ss AM/PM"))

Set criteria under that column to false (0) and see if
this query returns any records (I hope not!)

"Harry F."wrote:

Thanks for the reply. When I use the expression in the query without a
criteria, it does actually return the time value properly. However, when
I
set the criteria I need, a get the Data Type Mismatch error. Any
thoughts?

Regards,
Harry

"Gary Walter" wrote:

an oldie but a goodie

ACC2000: Query with Time Criteria Returns No Records from Microsoft SQL
Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;207700

**quote**
==========
Method 2
==========
Create the following expression in the query to extract the time portion
of
the field:
Expr1: CVDate(Format([<Name of Time Field>],"hh:mm:ss AM/PM"))

You can then enter the literal time value enclosed in number signs (#) on
the Criteria row of this expression.

**unquote**

"Harry F." wrote:
Hi Barry,

Tried the single quotes, that didn't work. How would I know what
Date/Time
type I'm using in SQL server? I don't have access to the actual SQL
server
app, just to the linked Access DB.

Thanks for the help,
Harry

"Barry Gilbert" wrote:

The only way that should make a difference is if you are using a
Pass-through
query, in which case you'd surround your date criteria with
single-quotes
instead of #'s. I don't think you'd get this exact error, though.

If you are using a standard Access query and just connecting your
tables
to
SQL Server with ODBC, Access should use DAO and therefore require the
#
signs.

Which date/time type are you using in Sql Server? I've seen issues
based
on
which one you choose, although I don't remember the issues at the
moment.

Barry

"Harry F." wrote:

Barry,

The source is definitely a date/time, and I also used the "#". Does
the
fact that the data is coming from a SQL server table via ODBC
matter?

Thank you,
Harry

"Barry Gilbert" wrote:

As long as the source column is a DateTime field and you surround
your
criteria in '#' signs, it should work. Setting up a parameter in
the
parameters list won't help.

Barry

"Harry F." wrote:

Marshall/Barry,

I should have noted that I tried that already, and got the
dreaded
"Data
Type Mismatch in Criteria Expression" message. Do I have to
define
a
criteria type somewhere (parameters?)?

"Barry Gilbert" wrote:

Look in help for the TimeValue function. It will return, you
guessed it, the
Time Value of the datetime field.

Create a new column like this:

TheTime: TimeValue(MyDateTimeField)
In this field's criteria, put:

#4:30 pm#

Barry

"Harry F." wrote:

Hi,

I have a field in my database that is Date/Time. I want to
return all
records with a time value after 4:30 pm, grouped by date.
Any
ideas on how
to do this?

Thanks,
Harry





.

Relevant Pages

  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... And make the person after you who is reading the query ... Tibor Karaszi, SQL Server MVP ... >>> improved performance in the problem area, ... >>> criteria is from the right hand table of a LEFT JOIN. ...
    (microsoft.public.sqlserver.server)
  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... And make the person after you who is reading the query ... Tibor Karaszi, SQL Server MVP ... >>> improved performance in the problem area, ... >>> criteria is from the right hand table of a LEFT JOIN. ...
    (microsoft.public.access.queries)
  • RE: time criteria in query
    ... "Harry F." ... I don't have access to the actual SQL server ... in which case you'd surround your date criteria with single-quotes ... Which date/time type are you using in Sql Server? ...
    (microsoft.public.access.queries)
  • Re: Accessing SQL data from an ACCESS app...
    ... When you are referring to writing an Access query that uses a VBA function ... for it's criteria to fill in the parameters (I've never even used SendKeys ... > database to SQL Server, using only ODBC linked tables to SQL server 2000. ...
    (microsoft.public.sqlserver.odbc)
  • Re: time criteria in query
    ... set the criteria I need, a get the Data Type Mismatch error. ... Query with Time Criteria Returns No Records from Microsoft SQL ... "Harry F." ... SQL Server with ODBC, Access should use DAO and therefore require the # ...
    (microsoft.public.access.queries)