Calculate days between two dates by VBA

[hhuuhn12]

Hello,
I am new for version 2010, previously i used macro "setvalue" it worked well, I tried DateDiff function, also failed.
I have a table and an application form for data entry, two fileds with dates, another field/control will be auto calculate days. I have tried using "on Click" or "on Enter" event procedure on that control, which is converted from macro. it didn't work. here is my code:

Private Sub Total_days_drug_used_Enter()
On Error GoTo Total_days_drug_used_Err
Forms!StudyDrugAccountabilityLog![Total days drug used] = Forms!StudyDrugAccountabilityLog![RETURENDATE] - Forms!StudyDrugAccountabilityLog![DISPENSEDATE]


Total_days_drug_used_Enter_Exit:
Exit Sub
Total_days_drug_used_Enter_Err:
MsgBox Error$
Resume Total_days_drug_used_Enter_Exit
End Sub



I have few knowledge of VBA , would greatly appreciate if you could guide me to get this done.
Thanks in advance.

[Bob Fitz]

Try:
=DateDiff("d", DISPENSEDATE, RETURENDATE)

as the Record Source property of a text box.

[June7]

I think Bob means the Control Source property of a textbox.

This is a calculated value and no need to save to field in table. Calculate when needed in a textbox or in a query. No VBA necessary.

[Bob Fitz]

I think Bob means the Control Source property of a textbox.

Thank you June. You are, of course, quite right.

[Dal Jeanis]

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:

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.

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.

[hhuuhn12]

Thank you all for the kind help.
I tried DateDiff, it was not working because I need it in the table, for which I need to calculate patient compliance with a formular ( also need help with that ). I think Dal Jeanis has provided me the perfect code that meet my needs thought I haven't tested yet.
Thanks millions for your expertise , i will take your suggestion changing the control name "txtDyaysUsed" i will certainly let you know after I done.

[Bob Fitz]

I tried DateDiff, it was not working because I need it in the table,

Why do you need the calculation in the table. I agree with June, that it would be better to do the calculation when ever and wherever the result is required.

[Dal Jeanis]

If you have all the fields in the table, then you COULD run an update query to set the values. You would probably HAVE to do so, if there is currently data in those fields and the difference hasn't yet been entered. You might have to run it every day, if the DISPENSEDATE or RETURENDATE can be changed any way other than the particular form with the code.

However, Fitz is right that there's no need to put it into the table at all. If the two date fields are there in the table, then any query can calculate the difference between the two dates, using the same or similar formula, or any control on a form can display the result of the calculation.

[hhuuhn12]

Now, I tried both VB code and datediff at control source of textbox. I found adding expression to control source is simple and helpful, I used the same way to have patient compliance calculation and it works. What I do is for clinic research, I don't need to run query everyday, for my situation, I thought it would be easier to have "total days drug used" and "total returened drug" in the table to calculate compliance.
Thank you all for the help!

[Bob Fitz]

Now, I tried both VB code and datediff at control source of textbox. I found adding expression to control source is simple and helpful, I used the same way to have patient compliance calculation and it works. What I do is for clinic research, I don't need to run query everyday, for my situation, I thought it would be easier to have "total days drug used" and "total returened drug" in the table to calculate compliance.
Thank you all for the help!

And how acurate will the stored calculation be if one of the dates gets changed with out updating the stored calculation.

[hhuuhn12]

Our situation is not complex, we usually update or change dates not directly in tables, but in application form, so when dates change, the calculation will also change in table, only after all data cleaned and finalized, we export table for statistical analysis. So what you saia shouldn' be occured.

[June7]

Then did you abandon saving the calculated data? Issue is resolved by doing calcs in query and/or textboxes?

[Bob Fitz]

Our situation is not complex, we usually update or change dates not directly in tables, but in application form, so when dates change, the calculation will also change in table, only after all data cleaned and finalized, we export table for statistical analysis. So what you saia shouldn' be occured.

The point I am trying to make is that if you do the calculation as, when and wherever it is needed, then the result will be more reliable than a value saved in a table. I believe this is usually regarded as "best practise".

[Dal Jeanis]

hhuuhn12 - Well, we won't beat you up about it. It just makes us apprehensive to know that if the saved date data gets altered, the saved duration data will be wrong.

That sense of apprehension is what I call "professional paranoia" - because if I ignore it too many times, the gremlins really WILL be out to get me.

[hhuuhn12]

Then did you abandon saving the calculated data? Issue is resolved by doing calcs in query and/or textboxes?

I save calculated data in textbox, I still think it would be good if I could save the calculated data in table.....