If else

[Jayfaas]

Hello,
I have a calibration database at work that I use to keep track of the calibration for tools in the warehouse, and the fields consist of ID number, Status(Active, Inactive), Due Date, Last Date, etc. I want to be able to set the Status field to be able to change it to inactive if the Due Date exceeds the current date. IE If the due date is 4/4/2012, and the current date goes past that (4/5/2012 -), I would like the status to change to inactive. We have an active query, and an inactive query, but right now we are having to go back in and change the status manually to inactive when we know its past due, but we have over 100 items to keep track of, and it would just be easier if it did it automatically. So is there a way I can do "IF Date < current date, make active. Else, inactive (or IF Date > current date, make inactive). Please help.

[ssanfu]

Hello,
I have a calibration database at work that I use to keep track of the calibration for tools in the warehouse, and the fields consist of ID number, Status(Active, Inactive), Due Date, Last Date, etc. I want to be able to set the Status field to be able to change it to inactive if the Due Date exceeds the current date. IE If the due date is 4/4/2012, and the current date goes past that (4/5/2012 -), I would like the status to change to inactive. We have an active query, and an inactive query, but right now we are having to go back in and change the status manually to inactive when we know its past due, but we have over 100 items to keep track of, and it would just be easier if it did it automatically. So is there a way I can do "IF Date < current date, make active. Else, inactive (or IF Date > current date, make inactive). Please help.

All you have to do is execute an Update query when the database is opened each day.

Something like (air code):

UPDATE TableName SET [Status] = "Inactive" WHERE [Due Date] < Date()

If the due date is equal to today, do you want the status set to Inactive? If so, change "<" to "<=".

If you have a startup (main, switchboard) form, try it in the Form load event. Or you could have a button on the form. Many ways to execute the code.

[pbaldy]

If status is always based on the date, I wouldn't save it and try to keep it updated (calculated field violation of normalization rules). Your active and inactive queries can simply run off the date.

[Jayfaas]

The way it works is....if the tool is past the calibration due date, it goes inactive, but we have to set it to inactive, meaning that we pull it off of the floor from being used until we get it calibrated. I want the database to be able to read the due date and if its past the current date then it would change the status field to inactive. The status field has Active, Inactive, Unknown, Rejected, and User Cal options. If I did do that code, where would I put it?

[ssanfu]

The way it works is....if the tool is past the calibration due date, it goes inactive, but we have to set it to inactive, meaning that we pull it off of the floor from being used until we get it calibrated. I want the database to be able to read the due date and if its past the current date then it would change the status field to inactive. The status field has Active, Inactive, Unknown, Rejected, and User Cal options. If I did do that code, where would I put it?

What form opens first? I would try that form's load event. It only has to run once a day, when the mdb if open the first time each day. If it runs more than one time, no harm.

[pbaldy]

I would create a utility db that just updated the field when it opened, then closes itself, and run it from Windows Scheduled Tasks when nobody is working in the db. While there would be no "harm" running the update more than once, it would be an unnecessary load and it would be much more likely you'd run into record locking issues.