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.)