<= doesn't work?

[CSAngel]

I have the following query:

SELECT [Risk Table].*
FROM [Risk Table]
WHERE ((([Risk Table].Type)="Risk") And (([Risk Table].Status)="Open") And
(([Risk Table].[Status Date])<=Forms!Main.txtDate));

If I enter January 29, 2009 in the txtDate text box I only get back records
with a status date BEFORE Jan. 29. It doesn't seem to capture the =. Any
suggestions?

 

[Stefan Hoffmann]

hi,

SELECT [Risk Table].*
FROM [Risk Table]
WHERE ((([Risk Table].Type)="Risk") And (([Risk Table].Status)="Open") And
(([Risk Table].[Status Date])<=Forms!Main.txtDate));

If I enter January 29, 2009 in the txtDate text box I only get back records
with a status date BEFORE Jan. 29. It doesn't seem to capture the =. Any
suggestions?

The data type of your field [Status Date] is Date/Time. If you have
filled it with Now(), then you have always a time part. An test for
equality must fail in these cases, because #2009/01/09# is not a
complete Date/Time value and it is interpreted as #2009/01/09 00:00:00#.
Thus the mismatch. You may use DateValue() for stripping the time part, e.g.

WHERE Type="Risk" AND Status="Open"
AND DateValue([Status Date]) <= DateValue([Forms]![Main].[txtDate]);


mfG
--> stefan <--

 

[CSAngel]

THANK YOU!

hi,

SELECT [Risk Table].*
FROM [Risk Table]
WHERE ((([Risk Table].Type)="Risk") And (([Risk Table].Status)="Open") And
(([Risk Table].[Status Date])<=Forms!Main.txtDate));

If I enter January 29, 2009 in the txtDate text box I only get back records
with a status date BEFORE Jan. 29. It doesn't seem to capture the =. Any
suggestions?

The data type of your field [Status Date] is Date/Time. If you have
filled it with Now(), then you have always a time part. An test for
equality must fail in these cases, because #2009/01/09# is not a
complete Date/Time value and it is interpreted as #2009/01/09 00:00:00#.
Thus the mismatch. You may use DateValue() for stripping the time part, e.g.

WHERE Type="Risk" AND Status="Open"
AND DateValue([Status Date]) <= DateValue([Forms]![Main].[txtDate]);


mfG
--> stefan <--

 

[Douglas J. Steele]

hi,

SELECT [Risk Table].*
FROM [Risk Table]
WHERE ((([Risk Table].Type)="Risk") And (([Risk Table].Status)="Open")
And (([Risk Table].[Status Date])<=Forms!Main.txtDate));

If I enter January 29, 2009 in the txtDate text box I only get back
records with a status date BEFORE Jan. 29. It doesn't seem to capture
the =. Any suggestions?

The data type of your field [Status Date] is Date/Time. If you have filled
it with Now(), then you have always a time part. An test for equality must
fail in these cases, because #2009/01/09# is not a complete Date/Time
value and it is interpreted as #2009/01/09 00:00:00#. Thus the mismatch.
You may use DateValue() for stripping the time part, e.g.

WHERE Type="Risk" AND Status="Open"
AND DateValue([Status Date]) <= DateValue([Forms]![Main].[txtDate]);

Wouldn't it be more efficient to use

SELECT [Risk Table].*
FROM [Risk Table]
WHERE ((([Risk Table].Type)="Risk") And (([Risk Table].Status)="Open") And
(([Risk Table].[Status Date])<=DateAdd("d", 1, Forms!Main.txtDate)));

or, written in a more presentable style

SELECT [Risk Table].*
FROM [Risk Table]
WHERE [Risk Table].Type="Risk"
AND [Risk Table].Status="Open"
AND [Risk Table].[Status Date]<=DateAdd("d", 1, Forms!Main.txtDate)

 

[Stefan Hoffmann]

hi Douglas,

Wouldn't it be more efficient to use
SELECT [Risk Table].*
FROM [Risk Table]
WHERE [Risk Table].Type="Risk"
AND [Risk Table].Status="Open"
AND [Risk Table].[Status Date]<=DateAdd("d", 1, Forms!Main.txtDate)

I don't think so, as it could produce wrong results in the first place.

When performance is necessary, you need a combined index on [Type],
[Staatus] and [Status Date]. Then


WHERE
[Risk Table].Type="Risk"
AND
[Risk Table].Status="Open"
AND
[Status Date] < DateValue(Forms!Main.txtDate) + 1 - 1/86400

should be the fastest. afaik there are some expresions, which can also
utilize an index, but I have to dive into my archives first...


mfG
--> stefan <--

 

[John W. Vinson]

AND [Risk Table].[Status Date]<=DateAdd("d", 1, Forms!Main.txtDate)

I don't think so, as it could produce wrong results in the first place.

Change the <= to < and it will produce exactly the right results, returning
all date/time values up to a few microseconds before midnight.