Date criteria help needed- i'm stuck :(

[Guest]

I have a database (Micro. 2000) I created that has all my subcontractor
information in. IE: adds, names, etc but the important part is the
certificates I'm required to have by law to have and they expire- I’m trying
to get the right expression for a query that the criteria will show me only
the subcontractors that have certificates that are expired. There are 3
different certificates that have dates of expiration and so far I've tried
the <=Date() expression and my thinking is that should show me the subs that
have forms that have expired on or before the current date. But it doesn’t
work and I’ve looked all over the help & how to try and wrap my brain around
this but simply I just don’t get it.

The database is made on 1 table (I know it should have been broken down but
I don’t get how to do all that linking and relationship stuff). The fields
are:
ID
FirstName
LastName
FirstName2
LastName2
CompanyName
MailingAddress
City
State
Zip
BusinessPhone
MobilePhone
HomePhone
BusinessFax
BusinessPhone2
Snow Sub? (Yes/no checkbox)
W-9 Form? (Yes/no checkbox) =Yes meaning OK/No meaning expired
W-9 Form Date Updated (Date Format)
Certificate of Insurance? (Yes/no checkbox) =Yes meaning OK/No meaning
expired
Certificate of Insurance Expires (Date Format)
Workman’s Comp Certificate? (Yes/no checkbox) =Yes meaning OK/No meaning
expired
Workman’s Comp Certificate Expires (Date Format)
Notes:
Dates Certs. Received
Email:

The ways I have to go thru it now is- manually go thru each record (on form
view) and click the yes/no box according to the certificates expiration date.
I would much rather to have the records automatically uncheck yes/no box if
the certificate has expired and check the yes/no box if it up to date. I am
not sure on how to go about that either. I could really use a lot of help on
this- appreciate any I get. TYVVM

~I brake for Hobbits~

 

[kingston via AccessMonster.com]

Use an update query to set the Yes/No field(s). Your date expression is
correct but maybe you're using it in the wrong place. Anyway, create a query
based on the table and make it an update query. Include a date field and the
corresponding Yes/No field. For the date field's criteria, use <=Date(), and
for the Yes/No field's Update To: expression, use 0. Run the query and all
records with dates on or before today will be unchecked. Do something
similar to check the box (use -1 instead of 0) and for the other
certification date.

 

[Guest]

you could write a procedure that will check the date for expiration and if
true, then check the [yes/no] as true.

ie....

if me.W-9 Form Date Updated = ?some date? then
me.W-9 Form{yes/no}field = true
else
me.W-9 Form{yes/no}field = true

this should get you started...

but if your looking for expired dates...then you need some sort of
constraint on the date fields.