How to find expired/missing data?

[pamk]

I would like to create a query for my employee database that tells me if any
of the following fields are expired -
auto insurance
drivers license
professional license
I'd like th have a report that only list the employee if one (or more) of
those fields has an expired date.

AND, I would like to create a report that would tell me the missing data in
a form. (i.e., an employee entered without a birthday, list the employee
name and the field missing the data).

Thanks!!!

 

[John Vinson]

I would like to create a query for my employee database that tells me if any
of the following fields are expired -
auto insurance
drivers license
professional license
I'd like th have a report that only list the employee if one (or more) of
those fields has an expired date.

What constitutes "expired"? Earlier than today's date? If so create a
Query based on the table with a criterion of

< Date()

on each of these fields. In the query grid put each such criterion on
a different line so that it uses "OR" logic, returning the record if
any one of the fields is earlier than today's date.

AND, I would like to create a report that would tell me the missing data in
a form. (i.e., an employee entered without a birthday, list the employee
name and the field missing the data).

Create a query using a criterion of

IS NULL

on each such field. Base a Form (for onscreen display) or Report (for
printing) on this query.

You may want to prevent such data from getting into the database in
the first place, by making these fields Required in the table design
window; or by putting VBA code in the data entry Form's BeforeUpdate
event checking to see if the fields are NULL prior to entering the
record into the table.

John W. Vinson[MVP]

 

[Jeff L]

I would like to create a query for my employee database that tells me if any

of the following fields are expired -
auto insurance
drivers license
professional license
I'd like th have a report that only list the employee if one (or more) of
those fields has an expired date.

To check to see if any are expired put
DateDiff("d",Date(),[YourFieldName])<0 in the criteria of each of your
fields. Make sure you put the criteria on separate lines so that it
will use OR, not AND.

Part 2


Select NameOfEmp, "Description1" as FieldMissing

From YourTable

Where Field1 is Null
Union
Select NameOfEmp, "Description2" as FieldMissing

From YourTable

Where Field2 is Null
Union
Select NameOfEmp, "Description3" as FieldMissing

From YourTable

Where Field3 is Null
..
..
..
etc
Obviously you need to replace your names in the query above.

Hope that helps!