using equals sign in a query that is source for a combobox

[mark r.]

To keep a long story short for now, I have a query that is
the source for a combobox.

Parameters [bdate]Datetime;
Select yada-yada
from table
where table.xdate < bdate ;
order yada yada

when I run the query I get prompted for a date, I type in
11/03/2002 and wallah I get results. But if I change the
less than sign to an equals sign I get an error.

expressions allows <,>,<=,>=, why not =
how do you do equals?

 

[Dirk Goldgar]

To keep a long story short for now, I have a query that is
the source for a combobox.

Parameters [bdate]Datetime;
Select yada-yada
from table
where table.xdate < bdate ;
order yada yada

when I run the query I get prompted for a date, I type in
11/03/2002 and wallah I get results. But if I change the
less than sign to an equals sign I get an error.

expressions allows <,>,<=,>=, why not =
how do you do equals?

I suggest you check the values in your table to see if they are set to
date-only values or if perhaps they contain date+time values. If this
field is set to the value returned by the function Now(), for example,
instead of Date(), then it contains a date + a time, and the only time
that will be equal to a date alone is if the time portion is 12:00AM.
For example, #8/27/2003 1:24AM# is *not* equal to #8/27/2003#.

If this is the problem, there are two approaches to fixing it. One is
to truncate all the values in the table field to make them contain dates
only, and fix whatever code assignes values to this field to use Date()
instead of Now(). That wouldn't be a satisfactory solution, of course,
if the time were important. The other solution is to revise your query
in one of two ways:

(a)
SELECT yada-yada
FROM table
WHERE DateValue(table.xdate) < bdate

(b)

SELECT yada-yada
FROM table
WHERE table.xdate >= bdate
AND table.xdate < bdate + 1

My guess is that for large tables, (b) will be noticeably faster than
(a), but it may not matter in your particular application.