calculated status field in column view (ms-access)

0
votes

A record in my table is processed in four steps. For each of the steps, a date is stored in the table (in fields like step1date..step4date). Upon completion of each step the appropriate date value is set. Steps can be skipped.

Now I'd like to calculate and show a status for the record. I've already written code which checks for the last non-null date field and finds the proper status value (something like "Step 2 upcoming").

The problem is that I want to display this calculated status value in a continuous form. I can't use an unbound field, so I'd normally use a query which calculates and provides the field for the form. Unfortunately I don't know how to manage this status calculation in a query. Can SQL do something like that?

The fallback solution is to add a status field to the table and calculate it in the form, but I don't like that for various reasons. How would you solve this kind of problem?

1
sqlms-access-2010
You might create a VBA function to compute the status and include that function in your query. If that's not a helpful suggestion, show us a brief example of the data you're dealing with and what you want as the computed status values for those data. - HansUp
How can I call a VBA function in the query with the current record? Currently I have a function in the form which returns the status string, I admit that I didn't try to call that from the query, because I thought it to be hopeless? - Batox
A query with a custom VBA function could look something like: SELECT MyFunction(date1, date2, date3, date4) AS status FROM YourTable ... where date1, date2, date3, date4 are fields in YourTable. - HansUp
That's it, and easy too. Thanks a lot! - Batox

1 Answers

1
votes

As it turns out I just had a problem understanding how to properly call a VBA function from a query (in this particular case: use ';' to separate parameters, not ','). Many thanks to HansUp!