By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

dcont within a loop returning error

P: 10
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
Expand|Select|Wrap|Line Numbers
  1. Public Function fixt()
  2. Dim Rst As DAO.Recordset
  3. Set Rst = CurrentDb.OpenRecordset("timefix")
  4. With Rst
  5. DoCmd.SetWarnings False
  6. Do Until .EOF
  7. Dim strSQLall As String
  8. Dim newtime As String
  9. newtime = "#08:30#"
  10.  
  11. If Rst![2ndtime] < "08:30:00" And Rst![ctype] = 5 Then
  12. Dim goodtime As Integer
  13. goodtime = Nz(DCount("ID", "timefix", "[2ndtime] < ""08:30:00"" AND [ctech] = " & Rst![ctech] & " AND [ctype] = 1"), 0)
  14. If goodtime <> 0 Then
  15.    strSQLall = "UPDATE [timefix] SET [newon] = """ & Rst![2ndtime] & """"
  16. strSQLall = strSQLall & " WHERE [ID]  = " & Rst![ID] & ";"
  17. DoCmd.RunSQL (strSQLall)
  18. End If
  19. Else
  20.  strSQLall = "UPDATE [timefix] SET [newon] = " & newtime & ""
  21. strSQLall = strSQLall & " WHERE [ID]  = " & Rst![ID] & ";"
  22. DoCmd.RunSQL (strSQLall)
  23. End If
  24. If Rst![2ndtime] >= "08:30:00" Then
  25.  
  26.    strSQLall = "UPDATE [timefix] SET [newon] = """ & Rst![2ndtime] & """"
  27. strSQLall = strSQLall & " WHERE [ID]  = " & Rst![ID] & ";"
  28. DoCmd.RunSQL (strSQLall)
  29.  
  30. End If
  31.  
  32. .MoveNext
  33. Loop
  34. End With
  35. 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.
Jun 15 '12 #1

✓ answered by Rabbit

Well there's your problem.

This is a string.
Expand|Select|Wrap|Line Numbers
  1. "12/31/2012"
This is a date.
Expand|Select|Wrap|Line Numbers
  1. #12/31/2012"
The same thing applies to time values.

Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,355
You haven't said what the error is.
Jun 15 '12 #2

P: 10
error is 3464 data type mismatch in criteria expression
Jun 15 '12 #3

P: 10
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
Jun 15 '12 #4

Rabbit
Expert Mod 10K+
P: 12,355
The error message is pretty clear. There's a data type mismatch. So what are the data types of the fields?
Jun 16 '12 #5

P: 10
Expand|Select|Wrap|Line Numbers
  1. 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.
Jun 16 '12 #6

Rabbit
Expert Mod 10K+
P: 12,355
Well there's your problem.

This is a string.
Expand|Select|Wrap|Line Numbers
  1. "12/31/2012"
This is a date.
Expand|Select|Wrap|Line Numbers
  1. #12/31/2012"
The same thing applies to time values.
Jun 16 '12 #7

P: 10
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
Jun 17 '12 #8

P: 10
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 :-)
Jun 17 '12 #9

Rabbit
Expert Mod 10K+
P: 12,355
Not a problem, good luck.
Jun 18 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.