Criteria syntax for sql

[Les Isaacs]

Hi All

This works:
DoCmd.RunSQL "UPDATE tblResults SET tblResults.athlete_time = '" & prevTime & "';"

and this works:
DoCmd.RunSQL "UPDATE tblResults SET tblResults.athlete_time = '" & prevTime & "' WHERE tblResults.athlete_number = 6;"

but this doesn't work:
DoCmd.RunSQL "UPDATE tblResults SET tblResults.athlete_time = '" & prevTime & "' WHERE tblResults.athlete_number = '" & thisAthleteNo & "';"
This fails with "Runtime error 3464 - data type mismatch in criteria expression".

The field 'athlete_number' in [tblResults] is of type Number (long integer).

Earlier in the function have:
Dim thisAthleteNo As Integer

and then:
thisAthleteNo = [No].Value

When I put a break point in the code on the DoCmd.RunSQL line, then hover over thisAthleteNo, it shows the expect integer value.

I'm sure this is a simple syntax error, but I've tried lots of alternatives but am now :banghead:

Hope someone can help.
Many thanks
Les

 

[delikedi]

I believe the problem is due to your attempt to pass a string where a long is expected. thisAthleteNo is a long, and should be passed as one, but the extra single quotation marks coerce it to be passed as a string.
Try:

DoCmd.RunSQL "UPDATE tblResults SET tblResults.athlete_time = '" &  prevTime & "' WHERE tblResults.athlete_number = " &  thisAthleteNo & ";"

 

[Les Isaacs]

Hi delikedi
That's it - removing the single quotes did the trick!!
Very many thanks
Les