mutiple date filters - query

[Guest]

Hi, please help if you can.

i need to filter data and have built a query but i cant get it to work.
there are 10 columns in the query each contains a date, or is null, which
represents when some one qualified for something.

One record in the might look like this for example:

Course 1 Course 2 Course 3 Course 4 Course 5 Course 6
1/05/02 17/04/05 1/01/06 6/07/04 8/08/03 7/7/07

if any of these dates goes expires i need to know to take remedial action.

the query looks like this:

Course 1 Course 2 Course 3 Course 4 Course 5 Course 6
<now() <now() <now() <now() <now() <now()
Or Is Null Or Is Null Or Is Null Or Is Null Or Is Null Or Is Null

am i doing this right?

grateful for your assistance.

Regards,

Paul

 

[Douglas J. Steele]

At the risk of offending, your table design is incorrect. You should never
have repeating groups like that. Instead, you should have a second table
that has one row for each course.

However, if you're stuck with the design, then no, you're probably not doing
it correctly, depending on what you're trying to do.

When you put multiple criteria on the same row in the query builder, you're
ANDing them together. In other words, what you will only return rows where
all 6 courses are earlier than today, or all 6 courses don't have a date.
I'm assuming that you want to know those rows where any one of the courses
is less than today or is null.

While it's possible to correct the query through the graphical interface,
you might find it easier to switch to SQL view (on the View menu) and
correct it there.

You'll have something like:

WHERE (((Courses.Course1)<Now()) AND ((Courses.Course2)<Now()) AND
((Courses.Course3)<Now())...) OR (((Courses.Course1) Is Null) AND
((Courses.Course2) Is Null) AND ((Courses.Course3) Is Null)...)

Change that to:

WHERE (Courses.Course1<Now() OR Courses.Course1 Is Null) OR
(Courses.Course2<Now() OR Courses.Course2 Is Null) OR (Courses.Course3<Now()
OR Courses.Course3 Is Null) ...

 

[Guest]

Many thanks Douglas,

Once i saw what you had done it was so obvious; thanks v.much.

Paul