dcont within a loop returning error

[nickstock]

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

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
 goodtime = Nz(DCount("ID", "timefix", "[2ndtime] < ""08:30:00"" AND [ctech] = " & Rst![ctech] & " AND [ctype] = 1"), 0)
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

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.

[Rabbit]

You haven't said what the error is.

[nickstock]

error is 3464 data type mismatch in criteria expression

[nickstock]

Perhaps someone could suggest a better way of achieving what i need.
so working with a single table that consists of date, techid, type of clocking, on time and off time + edited ontime and edited off time (not entire list or actual field names)
i need to do the following.
For selected date range i.e between 01/01/2012 and 08/01/2012 loop through all records and where the type of clocking = 5 check if its before 08:30 and if it is see if the same tech has a clocking type of 1 that that starts before 08:30 if so copy stat time to edited on time and if not set edited on time to 08:30.
i will also need to do simular for off times but one step at a time.
any suggestions welcomes as my brain is now well and truly pickled.
thanks Nick

[Rabbit]

The error message is pretty clear. There's a data type mismatch. So what are the data types of the fields?

[nickstock]

goodtime = Nz(DCount("ID", "timefix", "[2ndtime] < ""08:30:00"" AND [ctech] = " & Rst![ctech] & " AND [ctype] = 1"), 0)

id is autonumber, 2ndtime is long time, ctech is number, ctype is number.

[Rabbit]

Well there's your problem.

This is a string.

"12/31/2012"

This is a date.

#12/31/2012"

The same thing applies to time values.

[nickstock]

AH ha, flaming quotes lol i thought i needed a hash and as you can see i used them in the code above newtime = "#08:30#"
but i was using two hashes so i will try your method and let you know.
thanks
Rabbit

[nickstock]

Thanks for that it helped a lot as my mind was pickled, in fact i had tried that earlier on but as always with dates and times was unsure, but once i had that corrected i found a further error which was that i had in fact set [ctech] in the table to a Text field (doh) why the hell would i want a text field to store technician id's . well its getting there now and after that i have to alter the master table and correct any issues from the text field in the rest of the code.
Thanks again :-)

[Rabbit]

Not a problem, good luck.