query or VBA

[harleyken]

my subform is populated by a cbo box on main form. (i am aware naming isn't
w/conventions - but I am still new).

here is the query as it stands:
SELECT [Scheduled Classes].Class, [Scheduled Classes].[Class Start Date],
[Scheduled Classes].[Class Number]
FROM [Scheduled Classes]
WHERE ((([Scheduled Classes].Class)=[forms]![schedule class].[combo2]) AND
(([Scheduled Classes].[Class Start Date])>(Date()-1)))
ORDER BY [Scheduled Classes].[Class Start Date], [Scheduled Classes].[Class
Number];


this returns matching classes... however, I would like to include a "all
classes" (represented by "4" in the bound column of the cbo box).

can I modify my query to work, or should I use a cmd button on the form with
a IIf -Then statement and a select statement?

 

[Ken Sheridan]

You just need to amend the query's WHERE clause slightly. I'm assuming that
the Class column is text data type as you've wrapped the "4" in quotes. If
not leave out the quotes around it in the SQL snippets below.

If you want all classes with start dates on or after the current date:

WHERE ([Scheduled Classes].Class=[forms]![schedule class].[combo2]
OR [forms]![schedule class].[combo2] = "4")
AND [Scheduled Classes].[Class Start Date]>=Date()

If you want all classes regardless of start date:

WHERE ([Scheduled Classes].Class=[forms]![schedule class].[combo2]
AND [Scheduled Classes].[Class Start Date]>=Date())
OR [forms]![schedule class].[combo2] = "4"

Note that I've changed >Date()-1 to >=Date(). With the former its possible
that rows with a StartDate on the day before the current date could be
returned if the values inadvertently include a non-zero time of day, which
can creep in without you noticing unless you've taken steps in the table
design to forbid date/time values with non-zero times of day (in fact
Murphy's Law dictates that sooner or later they will inevitably creep in).
This because no such thing in Access as a date value per se, only a date/time
value, so >Date()-1 actually means after midnight at the start of yesterday.
You can see this by entering the following expressions in the debug window,
provided you don't do so on the exact stroke of midnight:

? Now()-1 > Date()-1

? Now()-1 >= Date()

The first will return True, the second False.

Ken Sheridan
Stafford, England