Computing the default value for a field (Access 97)

Topic

New Reply

I would like to have a field value in my Access 97 database automatically calculated based on the value in two other fields. The first field would be a date and the second field would be a period of days. For example, let’s say we have field one as “Date Entered”, field two as “Days to Complete” and then the last field, the one that needs to be calculated by adding the other two would be called “Date Due”.

How do I write the formula for field “Date Due” to add the other two field values and present a date?

THANKS!!

Drew

Reply | Quote

Replies

Just add the two fields:
Expr1: [Date Entered]+[Days to Complete]

Reply | Quote

Well, that’s precisely what I tried and it gave me an error message. If I am in Table Design mode, where am I to put that expression? Perhaps that’s where I erred.

Drew

Reply | Quote

Right. I don’t think table definitions allow calculated fields. As suggested above, do the calculation in a query based on the table or as the control source for a form or report field.

Besides, in general, it’s good practice not to bloat your tables too much with data that’s directly calculatable from other fields in the record.

Tom

Reply | Quote

You cannot create default values in a table that are dependent on another field in the table. The logical place to do this is in a form, which is where you should be entering data anyhow. However, as has been pointed out, it is bad design to capture data that can be calculated on the fly. If you did capture the calculated value, then it would need to be updated whenever anyone changed the field it was dependent on. And there is *no* way to do that in a table, only with a query or using a form.

Reply | Quote

Thanks everyone for your help! I see now where I was in error. It makes sense not to have a permanent calculated field in the data tables. I tried adding a text box to a form and set the control to the formula and bingo! it worked fine.

THANKS AGAIN!

Reply | Quote

Another approach is to use the DateAdd function. It is more useful if you want to add weeks or months (or years) as it lets you specify the interval you want to add. Check the help file for the syntax.

Another trick that doesn’t seem to be documented very often is that you can set a date equal to the last day of the month by specifying zero (0) for the day parameter in the DateSerial function. Very useful for dealing with leap years and Feb, April, Sept and Nov.

Lastly, as long as you want to do dynamic calculations, you don’t need to store the results in a field in a table – just put the expression in a query.

Reply | Quote