dynamic table fields?

[striker73]

Is there anyway to have a dynamic field in a table? For example, if I want to have a table with a field for a certain date and a field for a status. If the date is filled in, then the status field will automatically select another option. Does this make sense?

If a date is entered, the field could say "Complete" whereas if the date was not entered, it would say "Incomplete." Something to that effect. Can I do that within the design of the table or do I need to use VBA to code that in when I am updating the table? Thanks!

 

[JerryDennison]

No attribute (i.e. field) should be dependent on another attribute. This is the same as a calculated field, you're just returning words instead of numbers based on the value or lack of value in one field. Use an unbound field in a query to give you this result and calculate it on demand rather than store the value in your db.

 

[BNPMike]

As Jerry says, it is preferably not to have such transitive dependancies in your database. The reason is that you risk getting a date coupled with 'Incomplete' ie inconsistent information.

There is no law against it however. Typically you would do it to make life easier for yourself. Jerry's solution has the downside that you have to do the decode for every form, report etc that you have.

Some databases have triggers you can set at the table level so that as the data is changed the status is automatically set no matter what process is causing the update. This is not available as far as I know in Jet but may well be in MSDE (ie SQL Server) which is the other database that comes with Access.