How can I subtract 2 different dates in a form???

[hamerlund]

I have a form with the following text boxes. 'Creation Date', 'Scheduled Date', 'Completion Date' and 'Duration'. The form user enters the Creation date, the scheduled date and the completion date. How can I have the Duration textbox fill with the difference of the
Completion date - Scheduled Date. The Duration will have to be in weeks and days. Any help with this will be greatly appreciated.

chris

 

[AmberO]

You could use vb .....

Private Sub duration_GotFocus()
duration = DateDiff("d", [completion_date], [scheduled_date])
End Sub

This will calculate the difference between the dates in days - might be a good start for you. I put it in the 'on focus' event for the duration date, but you could put it in the on exit event for the completion date to have it appear directly after the user has updated this field.

For more info, look up datediff in the access help screens.

Amber

 

[67peshawar294]

Try this, make three textboxes;

1st txtbox (to figure the total number of days)
text54
In control source:
=DateDiff("d",[creationdate],[completiondate])

2nd txtbox (to figure the number of weeks)
text55
In control source:
=IIf(Int([text54]) Mod 7=0,[text54]/7,Int([text54]/7))

3rd txtbox (to figure the number of days)
text56
In control source:
=[text54] Mod 7

*The text box numbers 54,55,56 are arbitrary, yours will be different.
You can hide the 1st txtbox the 2nd make label "weeks" and the 3rd make the label "days."
Jim