I had some help on this one earlier, but I'm still having a bit of
trouble. I'm sure it's something simple that I just don't know.
I'm using Access2000. I have one table with employee salary
information. I need to calculate the bi-weekly gross pay (BWG) for
each employee. The BWG is slightly different for leap years. As the
BWG will have to be adjusted at the beginning and end of every leap
year, I want to be able to update all employees' BWG with one mouse
click. I've written a function in a module to do this, but I'm
getting an error "Data type conversion error." and the line
rs(fldBWG) = rs(fldSal) * 0.038251
is highlighted.
I'm not sure if this is the correct way to set the field vars, but I
did it like this:
Set fldSal = rs.Fields("Salary")
Set fldBWG = rs.Fields("BWG")
where Salary and BWG are field names in the table tblEmployees.
Both Salary and BWG are of data type Currency, format: Currency,
Decimal Places: 2.
When the code stops executing at the highlighted line:
fldSal has correct value of the field Salary of the first record.
Mousing over the 'rs' in rs(fldSal) gives the message <Data type
conversion error.>
fldBWG has current value of the field BWG of the first record before
the assignment (as the execution stops before the assignment has been
made.)
Mousing over the 'rs' in rs(fldBWG) gives the message <Item not found
in this collection.>
This last item seems to be the problem, but I don't know why it's "not
found in this collection."
Below is my entire code.
Any help (or suggestions of a better way to do this) is appreciated.
Public Function BWGUpdate()
' calcualte and display bi-weekly gross
Dim intYear As Integer ' current year
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fldSal As DAO.Field ' salary
Dim fldBWG As DAO.Field ' bi-weekly gross
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblEmployees")
Set fldSal = rs.Fields("Salary")
Set fldBWG = rs.Fields("BWG")
' get current year
intYear = Val(Right(Date, 4))
rs.MoveFirst
Do While Not rs.EOF
'check for leap year
If blnLeapYear(intYear) Then
rs(fldBWG) = rs(fldSal) * 0.038251 ' error occurs here
Else
rs(fldBWG) = rs(fldSal) * 0.038356
End If
rs.Update
rs.MoveNext
Loop
rs.Close
End Function
Public Function blnLeapYear(intYear As Integer) As Boolean
' check if current year is a leap year
' if 2/29/intYear is a valid date, we are in a leap year
blnLeapYear = IsDate("2/29/" & intYear)
End Function