Automating Date-Based Updates?

[Chris Ingram]

I am a new Access user. I have created a database to track and inventory animals brought to a local animal shelter. The database includes information such as when the animal was brought in, who dropped it off, when it was adopted (Hopefully!), etc. The database is being used by people who have limited computer experience and the challenge has been to automate it and simplify it as much as possible. I have created several Switchboard pages to try and accomplish this.

I have done research through Google and MS support and have found little help with a solution to this problem:

When an animal comes to the shelter, it is held for three days before it becomes available for adoption. I have created seperate fields for Date In Day, Date In Month and Date In Year. There is also a field detailing the animals adoptability status. This field is left blank in the intake process, and is then manually updated to Adoptable, Biter, etc., after the three day period. Is there a way to automate the Adoptability Assessment Field (which is a Lookup Table Field) so that it changes from blank to Adoptable after the three day period? (We have very few animals come in that are marked as Biters, so I just need the field to change over to Adoptable after three days have passed. Unless a solution can be presented to filter animals with Biter status out of the solution.)

I truly appreciate any help that can be offered.

 

[antomack]

One way to do this would be to have an update query run each time that the database is opened which would update the adoptability status on any animals who have been more than 3 days in the shelter and nothing has yet been entered in the adoptability status field.

Query similar to below
UPDATE YourTable SET AdoptabilityStatus = "Adoptable" WHERE (Date()-[YourDateInField])>3;

If you already have a field which stores the Biter status then you can take this into account as follows, if your biter status field is set to a text value of 'Yes' if the animal is a biter.
UPDATE YourTable SET AdoptabilityStatus = iif([BiterStatus]="Yes","Biter","Adoptable") WHERE (Date()-[YourDateInField])>3;

 

[Chris Ingram]

Thank you...

Thank you for your reply and advice.

The Biter status is not set up in a separate field. It is a choice on the dropdown list built into the Adoptability Assessment field. Every animal is eventually marked as Adoptable, a Biter, Sick, having Food Agression, Elderly, or to be Held for a Court Case. I set it up this way to generate what I call the Intake Form, which is accessed through a Switchboard button. The shelter employee just picks the status through the dropdown list.

I am certainly open to making changes to make this DB user friendly for my coworkers. Would we be better off having a seperate field for each status?

Again, I appreciate your time and efforts.

 

[wazz]

is it possible that an animal could have more than one of those assessments be applicable? or do you want to just keep it to one option?

 

[FoFa]

I would suggest, on your form where they can look up the animal, have it check each record as it is looked up and modify as needed.
Any report should come from a reports page, which can also run query against all the data to make the required change when the reports page is opened.
This way you don't have to worry about shutting down the system or leaving it running, trying to keep it all in sync, etc.
Just my 2 cents worth.