date field is Null but only if 2 other dates fields are not Nu

date field is Null but only if 2 other dates fields are not Nu

Post by SmFtaWV » Sat, 15 Apr 2006 02:17:01


Records are audited at 2 months, 4 months, and 6 months from a Start Date.
So dates are entered into the fields once teh record is audited at that time.
For the query, I want to know what records have not had the 6 month audit yet
(regardless if it is "overdue" or not).

Would I just add the 4 month and 2 month fields to the query deisgn and
enter "Not Is Null" in the critiera for each field?
 
 
 

date field is Null but only if 2 other dates fields are not Nu

Post by John Spenc » Sat, 15 Apr 2006 04:39:34

Put Is Not Null as the criteria for 2 and 4 months and Is Null as the
criteria for 6 months.

Do you ever have records that have a 4 month audit and no 2 month audit?
Or a 2 month audit, but are missing a 4 month audit and are now due for a 6
month audit? If so, you will need to do more complex criteria.

 
 
 

date field is Null but only if 2 other dates fields are not Nu

Post by SmFtaWV » Tue, 18 Apr 2006 22:33:02

It is possible that I may not have a date in the 2 mos or 4 mos fields as
described below. Any suggestions on teh criteria?
 
 
 

date field is Null but only if 2 other dates fields are not Nu

Post by John Spenc » Wed, 19 Apr 2006 02:14:52

The following would get records where audit2 has a value or Audit4 has a
value (or both have a value) and Audit6 has no value.

WHERE (Audit2 is Not Null OR Audit4 is Not Null) and Audit6 is Null

In the query grid, that would look something like
Field: Audit2
Criteria(1): Is Not Null
Criteria(2): Blank

Field: Audit2
Criteria(1): Blank
Criteria(2): Is Not Null

Field: Audit6
Criteria(1): Is Null
Criteria(2): Is Null