Using OrderDate as criteria, MS Access query fails to filter recor

[Guest]

Does date need special format? Why does select filter not work? Several
records are clearly present but not filtered out.

 

[Michel Walsh]

Hi,


The format can be important for a parameter you supply, but does not matter
for date (and time) already stored in the table, since there, it is stored
as a number of days (and portion of day, such as 0.25 = 6:00 AM) since 30th
December 1899 (2 days later it is the First of January 1900). Since the hour
can be stored, asking for an equality:

WHERE fieldName =#01/01/2000#

may exclude records where the date is as wanted, but with a time other than
midnight (00:00:00).

WHERE DateValue(fieldName) = #01/01/2000#

would be safer.


Note that when using a constant date with the # # delimiters, Jet tries,
first, to give a meaning to the date as if it was in the US order:
MM/DD/YYYY, but if it fails, such as for #23/12/20#, you may get surprises,
such as this being "understood" as 20th December 2023, or something else (on
my PC, where my preferred format is yyyy.mm.dd, the #23/12/20# is
understood as 20th December 2023), so use the US format if you use the # #
delimiter (there are exception to this rule, unfortunately).



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

The answer is pointing in the correct direction but I know I have not got the
SQL quite right. Afraid I am a novice at this part. I want to be able to
Filter by Selection in the Tab Form: (thus I am actually clicking on the
particular date desired and then on the Filter by Selection icon).

WHERE (((DateValue([Orders].[NewOrderDate]))=DateValue([neworderdate])))

Or, can I define somewhere in the properties that it is DateValue (as
opposed to time of day factor) alone that is to be filtered?
Thanks, Pete

 

[Michel Walsh]

Hi,


I would use


WHERE Orders.NewOrderDate >= SomeDate
AND Orders.NewOrderDate < SomeDate+1


Assuming SomeDate holds only a day (not time, well, midnight in fact), with
NewOrderDate "between" somedate and 24 hours (1 full day) added to somedate
should return all related records, even if the time is stored in
NewOrderDate.



If that does not work, maybe you are using some INNER JOIN that also filter
out our expected records. To check so, see if the expected records are
present without any WHERE clause (without any criteria). You may need a LEFT
JOIN if that occurs, rather than an INNER JOIN, but the good news would be
that the problem is NOT the criteria (the WHERE clause) !



Hoping it may help,
Vanderghast, Access MVP

The answer is pointing in the correct direction but I know I have not got
the
SQL quite right. Afraid I am a novice at this part. I want to be able to
Filter by Selection in the Tab Form: (thus I am actually clicking on the
particular date desired and then on the Filter by Selection icon).

WHERE (((DateValue([Orders].[NewOrderDate]))=DateValue([neworderdate])))

Or, can I define somewhere in the properties that it is DateValue (as
opposed to time of day factor) alone that is to be filtered?
Thanks, Pete

Hi,


The format can be important for a parameter you supply, but does not
matter
for date (and time) already stored in the table, since there, it is
stored
as a number of days (and portion of day, such as 0.25 = 6:00 AM) since
30th
December 1899 (2 days later it is the First of January 1900). Since the
hour
can be stored, asking for an equality:

WHERE fieldName =#01/01/2000#

may exclude records where the date is as wanted, but with a time other
than
midnight (00:00:00).

WHERE DateValue(fieldName) = #01/01/2000#

would be safer.


Note that when using a constant date with the # # delimiters, Jet tries,
first, to give a meaning to the date as if it was in the US order:
MM/DD/YYYY, but if it fails, such as for #23/12/20#, you may get
surprises,
such as this being "understood" as 20th December 2023, or something else
(on
my PC, where my preferred format is yyyy.mm.dd, the #23/12/20# is
understood as 20th December 2023), so use the US format if you use the #
#
delimiter (there are exception to this rule, unfortunately).



Hoping it may help,
Vanderghast, Access MVP