Runtime error on Switch

[Phil]

Hi,

I have the following code which gives me a runtime error. Can someone tell
me why. GradeKey is a numeric. I use the same type code with a text field
and it works fine with single quotes around the 0,1,2....etc.

strSQL = "UPDATE Students " & _
"SET GradeKey = Switch " & _
"([GradeKey] = 0, 1, " & _
"[GradeKey] = 1, 2, " & _
"[GradeKey] = 2, 3, " & _
"[GradeKey] = 3, 4, " & _
"[GradeKey] = 4, 5, " & _
"[GradeKey] = 5, 6, " & _
"[GradeKey] = 6, 7, " & _
"[GradeKey] = 7, 8, " & _
"[GradeKey] = 8, 9, " & _
"[GradeKey] = 9, 10, " & _
"[GradeKey] = 10, 11 " & _
"[GradeKey] = 11, 12) " & _
"WHERE GradeKey <> 12"

CurrentDb.Execute strSQL, dbFailOnError

Must be a syntax error, but I don't know.

Thanks,
Phil

 

[Douglas J. Steele]

Nothing pops out as to why it's failing, but if it's numeric, why not just
use arithmetic?

strSQL = "UPDATE Students " & _
"SET [GradeKey] = [GradeKey] + 1 " & _
"WHERE GradeKey <> 12"