Re: time criteria in query

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: freetexttable query never completes nor times out?
    ... Full text does not perform with a nested loop because the search is done for each iteration, if your server doesn't have much memory you will have a huge IO issue. ... When I get properties for the FT catalog, ... The company I am currently working for is a heavy consumer of SQL FTS ... dB (this time from the actual ASP page that passes the query to the ...
    (microsoft.public.sqlserver.fulltext)
  • Re: freetexttable query never completes nor times out?
    ... Full text does not perform with a nested loop because the search is done for each iteration, if your server doesn't have much memory you will have a huge IO issue. ... When I get properties for the FT catalog, ... The company I am currently working for is a heavy consumer of SQL FTS ... dB (this time from the actual ASP page that passes the query to the ...
    (microsoft.public.sqlserver.fulltext)
  • Re: freetexttable query never completes nor times out?
    ... if your server doesn't have much memory ... and the "executing query" ball just kept ... When I get properties for the FT catalog, ... The company I am currently working for is a heavy consumer of SQL FTS ...
    (microsoft.public.sqlserver.fulltext)
  • Re: freetexttable query never completes nor times out?
    ... SQL Server MVP ... This is a dev server so I hadn't even been ... When I get properties for the FT catalog, ... I just ran a similar query on ...
    (microsoft.public.sqlserver.fulltext)
  • 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)