How to set text box value based on the other boxes values

[lljj1234]

I have office 365. I am new to forms and macros. I am creating a split db (forms in access accessing linked tables in sql server). I have a table with 3 columns: StartDate (DATE), EndDate (DATE) and Duration (INT). I am creating an entry form that display those 3 fields as text boxes. I want to input dates into the first two fields then the number of weeks (calculated as (enddate-startdate)/7)) will be automatically shown in the 3rd field. How can I do that. Would I create an after update macro on each of the first two fields that somehow display number of weeks in the 3rd field? If so please guide me on how to code the macro.

(I initially created the sql server table with Duration as a computed column that calculated number of weeks based on the other two columns, however in access form it did show the value when I put in the two dates. So I now changed it to be just a integer field.


I also read on data macro. However data macro does not work for linked tables so I cannot use it.)

[CJ_London]

in your INT control try putting

=(nz([endate])-nz([startdate]))/7

in the controlsource.

Note you should not be storing this sort of calculation in your table, just calculate it when required

[lljj1234]

Thank you Ajax for the advice.

Yes I think your advice on not storing the calculated value in table is a good one. In this case I am helping a user to move table data from her Access to sql server. The Access db (and its 7 forms) was created by a person who is no longer around. To minimise work involved I will just modify the forms to access the linked tables.