Not sure where that code you posted would have been placed, but I would put the calculation code in the AfterUpdate events of the two individual controls that make up the calculation, assuming they are entered on this same form. I'm also naming the textbox control txtDaysUsed that the calculated days will go into.
That VBA would look like this:
Code:
Private Sub DISPENSEDATE_AfterUpdate()
On Error DISPENSEDATE_AfterUpdate_Err
Dim Day1 As Integer
Dim Day2 As Integer
Dim Day3 As Integer
Day1 = Int(NZ(Me.DISPENSEDATE))
Day2 = Int(NZ(Me.RETURENDATE))
If Day1>0 Then
If Day2>0 Then
' Dispensed and returned
' Count all days between including first and last
Me.txtDaysUsed = 1 + Day2 - Day1
Else
' Dispensed but not returned yet
' Count all days up to yesterday
Day3 = Int(Date())
Me.txtDaysUsed = Day3 - Day1
End If
Else
' Not dispensed yet
Me.txtDaysUsed = 0
End IF
DISPENSEDATE_AfterUpdate_Exit:
Exit Sub
DISPENSEDATE_AfterUpdate_Err:
MsgBox Error$
Resume DISPENSEDATE_AfterUpdate_Exit
End Sub
I'm taking advantage of the fact that, internally, a date is stored as the number of days from the beginning of the twentieth century(12/31/1899), so that INT(LaterDate) - Int(EarlierDate) is the number of days between those dates. You could also use DateDiff("d",,) to get the same results.
Here's the equivalent code for the RETURENDATE textbox.
Code:
Private Sub RETURENDATE_AfterUpdate()
On Error RETURENDATE_AfterUpdate_Err
Dim Day1 As Integer
Dim Day2 As Integer
Dim Day3 As Integer
Day1 = Int(NZ(Me.DISPENSEDATE))
Day2 = Int(NZ(Me.RETURENDATE))
If Day1>0 Then
If Day2>0 Then
' Dispensed and returned
' Count all days between including first and last
Me.txtDaysUsed = 1 + Day2 - Day1
Else
' Dispensed but not returned yet
' Count all days up to yesterday
Day3 = Int(Date())
Me.txtDaysUsed = Day3 - Day1
End If
Else
' Not dispensed yet
Me.txtDaysUsed = 0
End IF
RETURENDATE_AfterUpdate_Exit:
Exit Sub
RETURENDATE_AfterUpdate_Err:
MsgBox Error$
Resume RETURENDATE_AfterUpdate_Exit
End Sub
By the way, "Total days drug used" is a really weird name for a control. It is much preferable to avoid spaces in the names of fields, controls, tables and such.