I need a field to update based on other fields.

[zgray]

I need a field to update based on other fields.

Dont think they want me to give specifics on what im working on but, I have 3 date fields. Date1, date2, and date3. I have a status field that need to change based on if a date is entered.

For example. If there is a date in Date1 field, the status needs to update to blue. If theres a date in Date2 it needs to update to red... etc.

Hope this is clear on what I'm asking.

 

[boblarson]

Okay, you're not going to like this but we try to provide best practices info here.

1. It sounds like you should NOT have 3 date fields.

2. It sounds like you should have another table which stores the dates along with the "project ID" or whatever they are tied to.

3. You don't store the status but you use a QUERY at the time you need it to calculate the status. Storing the status, based on the entry of the other field, violates normalization rules (as well as having 3 date fields for the same thing) and it also puts the integrity of your data at risk.

4. You can use a table in 99% of the places you could use a table and, if your users need that status for some other thing, they should be using a query you put together for them instead of the table.

 

[zgray]

I'm not sure i get what you mean. The 3 date fields are used many times in the database so I do need them. All i need is for the status of the record to change based on if there is a date in the field or not and certain dates over ride other dates.