Not sure why this code isn't working - calculates datediff

[Joanne]

Hello, I have a subform in Access 2003 with these fields, type of day,
OLPEndDate, OLPBeginDate. At the end of each row, I want to calculate the
total day taken for that particular vacation request, so if a person took
12/1/2009-12/2/2009 then the last field "TotalOLPTaken" should calculate as
1. What's happening is that it does calculate but all of the rows calculate
to the same number. If I put the cursor in the second row and that row
happens to have 12/4/2009-12/6/2009, then even the row with 12/1-12/2
calculates as 2. Then if I put the cursor back in the first row, everything
calculates as 1. I realize I have the even on "Got Focus" but I've tried it
with every possible trigger and it always calculates the same way. Any help
would be appreciated.
Private Sub txtTotalOLPTaken_GotFocus()
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)
MsgBox dStart & " " & dEnd
Me.txtTotalOLPTaken.Value = dTaken

End If

End Sub

 

[Dirk Goldgar]

Hello, I have a subform in Access 2003 with these fields, type of day,
OLPEndDate, OLPBeginDate. At the end of each row, I want to calculate the
total day taken for that particular vacation request, so if a person took
12/1/2009-12/2/2009 then the last field "TotalOLPTaken" should calculate
as
1. What's happening is that it does calculate but all of the rows
calculate
to the same number. If I put the cursor in the second row and that row
happens to have 12/4/2009-12/6/2009, then even the row with 12/1-12/2
calculates as 2. Then if I put the cursor back in the first row,
everything
calculates as 1. I realize I have the even on "Got Focus" but I've tried
it
with every possible trigger and it always calculates the same way. Any
help
would be appreciated.
Private Sub txtTotalOLPTaken_GotFocus()
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)
MsgBox dStart & " " & dEnd
Me.txtTotalOLPTaken.Value = dTaken

End If

End Sub

If you have an unbound text box on a continuous form, it always has the same
value for all records. Use a calculated text box instead, with its
ControlSource set to something like this:

=IIf(Type_of_Day In("Ill","Vacation"), DateDiff("d", OLP_Begin_Date1,
OLP_End_Date1), 0)

Please note: the above line will have been broken onto multiple lines by the
newsreader, but it should be entered all on one line in the ControlSource
property.

Alternatively, you could create a calculated field in the form's
RecordSource query, and bind the text box to it.

If I've understood correctly, you do not need the event procedure you posted
at all.

 

[chanu]

Hi joanne,
please use # signs with the dates so that access could identify that these
are dates.otherwise Access would think you are dividing 12 by 4 and dividing
the result with 2009. so, replace the following line
dTaken = DateDiff("d", dStart, dEnd)
with
dTaken = DateDiff("d", #dStart#,#dEnd#).
Try this and see
from
chanakya
Baruva

 

[Dirk Goldgar]

Hi joanne,
please use # signs with the dates so that access could identify that these
are dates.otherwise Access would think you are dividing 12 by 4 and
dividing
the result with 2009. so, replace the following line
dTaken = DateDiff("d", dStart, dEnd)
with
dTaken = DateDiff("d", #dStart#,#dEnd#).
Try this and see

I'm sorry, but this is wrong. If dStart and dEnd are variables holding Date
values, then it would be wrong to try to enclose them in the "#" delimiter,
which is used only for date literals.

 

[Bob Quintal]

Hello, I have a subform in Access 2003 with these fields, type of
day, OLPEndDate, OLPBeginDate. At the end of each row, I want to
calculate the total day taken for that particular vacation
request, so if a person took 12/1/2009-12/2/2009 then the last
field "TotalOLPTaken" should calculate as 1. What's happening is
that it does calculate but all of the rows calculate to the same
number. If I put the cursor in the second row and that row
happens to have 12/4/2009-12/6/2009, then even the row with
12/1-12/2 calculates as 2. Then if I put the cursor back in the
first row, everything calculates as 1. I realize I have the even
on "Got Focus" but I've tried it with every possible trigger and
it always calculates the same way. Any help would be appreciated.
Private Sub txtTotalOLPTaken_GotFocus()
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)
MsgBox dStart & " " & dEnd
Me.txtTotalOLPTaken.Value = dTaken

End If

End Sub

Your problem is that you are calculating a value for display in an
unbound control. In a continuous form all instances of an unbound
control take on the value of the current record.

Move the calculation to the query that underlies the form,