Hi all im struggling with this and some help would be great.
overview: ive written a workshop time and attendance program and all works great however my boss has found that when doing a wages print out to show in and out times for the week some staff arrive early for the shift and remain on idle time until after shift starts, thus they get paid too much time, so i decided to write a function that will run when loading the wages section before displaying, it will basically check clock in time against shift start time and then write the correct time in a new field, this will only happen if the staff member has not clocked onto a job before shift starts allowing for genuine overtime to be paid.
to do this i created a query to work within the dates we are dealing with and the clocking types involed, the basic update works ok with a loop but when i tried to ensure the genuine early clockings are retained i used a dcount to find if that staff member had infact clocked onto a job before shift start.
here is my code
the bit thats causing the error is the goodtime im wondering if its because its inside a loop but i cant see why i wouldent work unless its some querky access thing, i know ive got lots more coding to do to get this right for a weeks worth across ten staff but until i get past this i cant move forward.
Thanks in advance
Nick
p.s the only time it runs ok is if i remove the [ctype] and [2ndtime] expressions from the dcount.
overview: ive written a workshop time and attendance program and all works great however my boss has found that when doing a wages print out to show in and out times for the week some staff arrive early for the shift and remain on idle time until after shift starts, thus they get paid too much time, so i decided to write a function that will run when loading the wages section before displaying, it will basically check clock in time against shift start time and then write the correct time in a new field, this will only happen if the staff member has not clocked onto a job before shift starts allowing for genuine overtime to be paid.
to do this i created a query to work within the dates we are dealing with and the clocking types involed, the basic update works ok with a loop but when i tried to ensure the genuine early clockings are retained i used a dcount to find if that staff member had infact clocked onto a job before shift start.
here is my code
Code:
Public Function fixt()
Dim Rst As DAO.Recordset
Set Rst = CurrentDb.OpenRecordset("timefix")
With Rst
DoCmd.SetWarnings False
Do Until .EOF
Dim strSQLall As String
Dim newtime As String
newtime = "#08:30#"
If Rst![2ndtime] < "08:30:00" And Rst![ctype] = 5 Then
Dim goodtime As Integer
[U]goodtime = Nz(DCount("ID", "timefix", "[2ndtime] < ""08:30:00"" AND [ctech] = " & Rst![ctech] & " AND [ctype] = 1"), 0)[/U]
If goodtime <> 0 Then
strSQLall = "UPDATE [timefix] SET [newon] = """ & Rst![2ndtime] & """"
strSQLall = strSQLall & " WHERE [ID] = " & Rst![ID] & ";"
DoCmd.RunSQL (strSQLall)
End If
Else
strSQLall = "UPDATE [timefix] SET [newon] = " & newtime & ""
strSQLall = strSQLall & " WHERE [ID] = " & Rst![ID] & ";"
DoCmd.RunSQL (strSQLall)
End If
If Rst![2ndtime] >= "08:30:00" Then
strSQLall = "UPDATE [timefix] SET [newon] = """ & Rst![2ndtime] & """"
strSQLall = strSQLall & " WHERE [ID] = " & Rst![ID] & ";"
DoCmd.RunSQL (strSQLall)
End If
.MoveNext
Loop
End With
End Function
Thanks in advance
Nick
p.s the only time it runs ok is if i remove the [ctype] and [2ndtime] expressions from the dcount.
Comment