Access Calculated field in form or in table?

1
votes

What's better for database consistence? A calculated field in a Form or in a Table?

e.g I have a Table which has some Date Fields! I want to calculate them so I can to update status.

[Start Date][End Date][Status]

I have a form to insert "Start Date" and "End Date"! So where is better to calculate it?

Thanks in advance!

1
formsms-accessms-access-2007ms-access-2010
What is the calculation which determines [Status]? – HansUp
iif( [End Date] - [Start Date] <= 10 ; "On time" ; "Delayed" ) – RooseveltJr
OK, you have a fairly simple expression to derive [Status]. With Access 2010, you can make that a calculated field in the table. However, why? You can use the same expression in a query to derive [Status] whenever you need it. In a form, you could use a similar expression as the Control Source property of a text box --- in that case, [Status] could update automatically whenever either of the date values change, even before those changes are saved to the table. With respect to consistency, that would only be a problem if you store [Status] in the table. – HansUp
True story! Gonna do That! thanks! – RooseveltJr

1 Answers

0
votes

Another option for you besides what HansUp has mentioned is to create a stored query that includes that expression as a value, then make the form based off that query. That way you have the advantage of accessing status AS IF it's a column in the table (although it will be read-only).