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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following fields in a query: 2 months, 4 months, and 6 months.

What I need to do in a query is to "filter" the 6 months field as Null but
the 4 month and 2 month fields are not Null. How would I show the criteria
for this query?
 
This sounds like you have a basic data design problem. What are the uses of
these fields?

-Amy
 
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?
 
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.
 
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?
 
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
 
Back
Top