date criteria does not work

[Guest]

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

 

[Tom Ellison]

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

 

[Guest]

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

 

[Guest]

Tom
Thank you very much
jer
--
thanks as always for the help

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