time criteria in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Harry said:
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?
Click to expand...


SELECT *
FROM table
WHERE TimeValue(datefield) > #16:30#
ORDER BY datefield
 
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#
Click to expand...

Barry
 
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?)?
 
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
 
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
 
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
 
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
 
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
 
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**
 
Hello Harry.

Harry F. said:
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?
Click to expand...

In a query, "grouping" means "create a summary for all records in the
group. Therefore, you cannot display details like "all records with a
time value after 4:30 pm" inside the group.
But you could sort by YourField and filter the records.
Therefore, you should extract the date and the time valiues from
your field.
Date: CDate(Int(YourField))
Time: CDate(YourField - Int(YourField))

I hope this helps.
 
Hi Gary,

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 said:
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. said:
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
Click to expand...
Click to expand...
 
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 said:
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. said:
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

:

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

:

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

:

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

:

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?)?

:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
what I wish I'd typed...

SELECT
[DateField],
IsDate([DateField])
FROM
yurLinkedTable
WHERE
IsDate([DateField]) = 0;
 
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 said:
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 said:
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**

:
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

:

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

:

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

:

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

:

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?)?

:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
Sorry Harry...I have a few questions I hope
you don't mind answering....

1) what is the difference between
DateReceived and DMReceived

2) what is result of

SELECT
[DateReceived],
IsDate([DateReceived])
FROM
qryProgeonProducts
WHERE
IsDate([DateReceived]) = 0;

or (depending on answer to q. 1)

SELECT
[DMReceived],
IsDate([DMReceived])
FROM
qryProgeonProducts
WHERE
IsDate([DMReceived]) = 0;

3) what is the SQL for qryProgeonProducts

thank you....



Harry F. said:
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;
Click to expand...
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.
Click to expand...
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?
Click to expand...
 
do you have a system DNS defined for
the SQL SERVER db?

if so, something that may help....

start a new query (don't add any tables)

go to top menu and choose

Query/SQL Specific/Passthrough

enter the following (replacing 'yurtable'
with actual SQL name of table that contains
"datetime" field this all is coming from)

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,
DATETIME_PRECISION, CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE, COLUMN_DEFAULT
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'yurtable'

click on red exclamation to run,
and in resulting dialog
point to your system DNS

what results do you get for the "datetime" field?
 
Back
Top