date criteria does not work

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jer - 20 Mar 2006 22:46 GMT

Some help please. Daily I am importing an excel worksheet into access to be
able to generate reports. The orignal date field in excel is formatted as
date time as my reports are base on differences in time. I want the user to
input a date and generate a report only for that date so in my criteria I
have [enter date]. When I enter a date for which I have imported date, I get
no result. I have tried adding a column to the query - datevalue([date]).
When I run the query with this I see the dates formatted as mm/dd/yyyy.
However when I put [enter date] in the criteria for this column, the query
does not return any records. Any suggestions, am I missing something here

Signature

thanks as always for the help
jer

Reply to this Message

Tom Ellison - 20 Mar 2006 22:54 GMT

Dear Jer:

It is important to always remember that formatting is a way to determine how
and what is displayed. In this case, it is suppressing the display of the
time of day, and showing only the date. However, the time of day is still
there.

Just be sure to use the DateValue function in the criterion as well.
DateValue([date]) = [enter date]

This way the entered date is compared with only the date portion of the
value in that column, not the date and the time together, which would not
usually match, so you get no rows of results.

Tom Ellison

> Some help please. Daily I am importing an excel worksheet into access to
> be
[quoted text clipped - 9 lines]
> However when I put [enter date] in the criteria for this column, the query
> does not return any records. Any suggestions, am I missing something here

Reply to this Message

jer - 21 Mar 2006 02:41 GMT

Tom
Thank you very much
jer

Signature

thanks as always for the help

> Dear Jer:
>
[quoted text clipped - 25 lines]
> > However when I put [enter date] in the criteria for this column, the query
> > does not return any records. Any suggestions, am I missing something here

Reply to this Message

Ken Sheridan - 20 Mar 2006 23:07 GMT

Try declaring the parameter as DateTime in the query. Without a parameter
declaration a date entered in short date format could be interpreted as an
arithmetical expression. This would evaluate to a number which would
correspond to a date/time value (which Access stores as 64 bit floating point
numbers as an offset from 30 December 1899 00:00:00) but not one likely to be
present in your table. To declare the parameter you add a line, terminated
with a semi colon, at the start of the query:

PARAMETERS [enter date] DateTime;
SELECT *
FROM MyTable
WHERE MyDate = [enter date];

Ken Sheridan
Stafford, England

> Some help please. Daily I am importing an excel worksheet into access to be
> able to generate reports. The orignal date field in excel is formatted as
[quoted text clipped - 5 lines]
> However when I put [enter date] in the criteria for this column, the query
> does not return any records. Any suggestions, am I missing something here

Reply to this Message