Calculating the difference between two fields

[Joanne]

Hello,
I have a subform in a table which has the following controls:
Type of Day....Begin Date.....End Date....Total Days

There may be several rows of information in these controls. The form is
used to calculate the number of vacation days a person has taken. I have
written some code that uses that DateDiff function to give me the difference
between Begin Date and End Date. The result is supposed to go into the TOTAL
Days control. My problem is that it appears that the calculation is
performed only once. So for instance, if John Doe has as his first row Begin
Date 3/13/2007 and End Date 3/15/2007, the Total Days calculates 2 days,
which is correct, but then on the next line if he has 4/15/2007 as the begin
date and 4/21/2007 as the end date, it still give me "2" as the total days.
Here is the code I wrote:

Private Sub Form_Load()
Dim dTaken
Dim dStart
Dim dEnd
dEnd = Me.OLP_End_Date1.Value
dStart = Me.OLP_Begin_Date1.Value

If Me.Type_of_Day.Value = "Ill" Or Me.Type_of_Day.Value = "Vacation" Then
dTaken = DateDiff("d", dStart, dEnd)
Me.Text22.Value = dTaken

End If
End Sub

 

[Douglas J. Steele]

Your code only looks at the data from the first row.

Realistically, though, you don't want to do that in the form's Load event.
Instead, since you don't need to store the calculated value, the Total Days
text box doesn't need to be bound, so you can put the calculation as the its
ControlSource property.

 

[Joanne]

I do have it as an unbound text box in the form and I have VBA code.
Sometimes there is only one row and sometimes there might be 7 or 8 or more
rows, depending on how many vacation days an employee has taken. How can I
code a text box that may or may not be there? What I'm trying to do is if
there are 7 rows of days, then each row will be calculated separately. But
as I said, the text box only shows up if the row shows up. I hope this
clarifies it.

 

[Douglas J. Steele]

Not sure what you mean by having VBA code.

What I'm suggesting is that the ControlSource for the text box be

=DateDiff("d", [BeginDate], [EndDate])

If you're trying to set the text box's value in VBA code, it will apply to
all the text boxes when they're unbound.