Runtime error on Switch

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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"
 
Thanks Douglas,

That worked great. I guess because of my lack of SQL knowledge I didn't
even think of it........

Douglas J. Steele said:
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"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Phil said:
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
Click to expand...
Click to expand...
 
Back
Top