Here is my quandary. I have this Access 2003 database, and it records, for simplicity's sake, expiration dates. The expiration date column can contain null values.

Then, there is a column right next to it for "status". I want the value of that field to be either "Current", "Expiring Soon", "Expired", of "Missing" dependant on the expiration date in relation to Now().

I've never done anything like this before inside a table, I've always done it programmatically in front end applications, which isn't a problem, but these values are going to need to be determined by the database table its self, like... I'm not even sure how this is going to work.

DESIRED RESULT: Get a report of all the dates that are Expiring Soon. (and other similar reports).

So, this going to have to be something that... either is done in the background whenever the database is opened or..done... When the user elects a certain report (which I really don't want to do...it sounds really... resource hoggy).

Tips, suggestings, tricks, ideas, anyone. I'm all open.

You can't because Access does not allow to base the contents of a column in a table on a user-defined function, and Access does not support triggers that would allow an automatic update of the computed column.

The closest solution would be to run an UPDATE query on the table every time the database is open, but it's on behalf of the front-end opening the database (or the table anyway).

Moreover, there is seldom a real good reason to store the result of a function (computed value) into a table. Such a value is dynamic (it can change more or less often) and is better handled "on the fly", when it's needed (Query, Form or Report).

the status column should be a fioeign key to another table, use either a numeric or text value, doesn't matter which. For this sort of thing I tend to use a text value which acts as a human readable code)

then when you run your form/report use a query you specify what code or codes you need in that query

another advantage of using a table for this (even with so few status codes is that if some tosspot user decides to add another status code, THEY can.

After a few hours of thinking about it, I was beginning to lean in that direction... just run a query when it's needed...or a stored proc- OH WAIT. Not using SQL server. Man I'd love to just have a trigger. -_-;;

I really wish I had addressed this when I was first designing the database... but truth be told, this was the first database application I ever ever developed professionally (haha, yeah...), so it was all... kind of a learning process.

Thank you for your advice!

I think what I'm going to do is just write an update query and pretend that the main switchboard onLoad() event is a trigger.

I would recommend calculating the status. You can easily do this by creating a Function in a module, and then adding a field to the query that is feeding the report or form. If you use an UPDATE query the data will be stale. I would also agree with Healdem create a small table with your statuses in it:

Status ID---------StatusDescription
1-----------------Expiring Soon
2-----------------Expired
.
.
etc.

Then create a little function that uses the date to determine the status:

Then add a field in your query GetStatus([ExpirationDate]) As Status. And finally join the Status field with the tblStatus to get the StatusDescription.

The other benefit to having a tblStatus table is if someone wants the status decriptions to be different you only have to change it once in the table.