RE: time criteria in query

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
    ... "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: 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)
  • Re: mm/dd/yyyy format question
    ... I was not talking about how .Net or SQL Server stores date/time values ... I get the impression that you are talking about ISO 8601 in terms ... applications use the format for unambiguously representing date/time ...
    (microsoft.public.dotnet.languages.vb)
  • 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: data wont be displayed in the form
    ... criteria specified in the SQL server view, ... > form because it doesn't satisfy the criteria in the underlying record source" ... > There are no filters, no server filters, no form filters, no where clauses. ...
    (microsoft.public.access.modulesdaovba)
Loading