Calculate based on two date fields, populate third which writed to

  • Thread starter Thread starter AndyG
  • Start date Start date
A

AndyG

MSAccess03. Scenerio: three text boxes (In; Out; Diff;) of type date.
User inputs date into 'In', moves to 'Out' and inputs date. I would like the
difference in the dates to do two things 1) populate box 3 'Diff', and also
2) to have the value in the box be written to the db.

I know I can do: DateDiff("d",In,Out) to get the difference,
however, what would the code look like to populate that box?
Would it be on 'Lost Focus' of the Out box? and what I really don't
understand is, even if I somehow put the value of the formula into
the third box, how does that get written to the db if the third box is
using a formula (unbound?)???

Help is greatly appreciated.
 
Realistically you need some way of knowing that both dates have been entered
and are the correct dates. It's possible that the user could accidentally put
in a wrong date, then go back and change it. So you can't just assume that as
soon as both dates have been entered that it's safe to write the new value.
You may want a button they click that says the dates are entered and are
correct, then have the button click event write the DateDiff("d",In,Out)
value to your table. I don't know if you have a bound form, or if you're
opening a recordset to write to a table or what, not really sure exactly what
this part of your app is doing, so I'm not sure what to tell you on exactly
how to get that value into the field.
 
It is incorrect to store calculated data. It should be calculated when you
need to present it to the user either on a form, in a report, or when
exported.

The proper technique would be to use the control source of the third text
box to display the difference using the DateDiff function.

=DateDiff("d", Me.In, Me.Out)

If either of the dates have not been entered, the results will be Null, so
nothing will show in the text box until both dates are entered.
 
Back
Top