Re: time criteria in query

Here is the SQL as it works (ie. provides a time value):

SELECT qryProgeonProducts.DateReceived, qryProgeonProducts.Product,
Count(qryProgeonProducts.Product) AS CountOfProduct
FROM qryProgeonProducts
GROUP BY qryProgeonProducts.DateReceived, qryProgeonProducts.Product;


Here it is when I get the error:

SELECT qryProgeonProducts.DateReceived, qryProgeonProducts.Product,
Count(qryProgeonProducts.Product) AS CountOfProduct
FROM qryProgeonProducts
WHERE (((CVDate(Format([DMReceived],"hh:nn:ss AM/PM")))>#12/30/1899 16:30:0#))
GROUP BY qryProgeonProducts.DateReceived, qryProgeonProducts.Product;


"Gary Walter" wrote:

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: SQL Syntax Error
    ... match all the criteria you input. ... SQL view and never open it in the query grid view. ... The syntax for the "where" statement is incorrect becuase of this. ...
    (microsoft.public.access.queries)
  • 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: Using combo box to search form
    ... "Jeff Boyce" wrote: ... Since you "started adding" additional criteria, ... about SQL syntax to know how to form a grammatically-correct SQL statement. ... One way to do that is to see what Access generates when you build a query. ...
    (microsoft.public.access.formscoding)
  • Re: Adjusting SQL in Query
    ... from the RESPEL table which satisfy the criteria in the PRICELIST Table. ... RESPEL INNER JOIN PRICELIST ON RESPEL.PRICELIST = ... Subject: Adjusting SQL in Query ...
    (microsoft.public.access.queries)