RE: time criteria in query

DateTime allows values down to 3.33 milliseconds. SmallDateTime allows down
to the minute. I suppose you could try entering milliseconds in Access and
see if it saves it, but like I said, I'm not sure if this is the issue.

Barry

"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: time criteria in query
    ... Or...start a query with just the date field ... "Harry F."wrote: ... criteria, it does actually return the time value properly. ... SQL Server with ODBC, Access should use DAO and therefore require the ...
    (microsoft.public.access.queries)
  • RE: time criteria in query
    ... Tried the single quotes, that didn't work. ... 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: 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)
  • RE: time criteria in query
    ... in which case you'd surround your date criteria with single-quotes ... If you are using a standard Access query and just connecting your tables to ... Which date/time type are you using in Sql Server? ... "Harry F." ...
    (microsoft.public.access.queries)
  • Re: need help creating criteria for time
    ... Both fields are Date/Time type. ... Allen Browne - Microsoft MVP. ... I then removed the criteria and left everything else and ran ... If you switch your query to SQL View (View menu, ...
    (microsoft.public.access.queries)