Another query problem 4

[jpl458]

Had to Change a query that I got help on from here, here is the new version

udq = _
"UPDATE BayCounts " & _
"SET [Count]=" & rsdbo.Fields("StCount") - rsdbo.Fields("SumOfBalls") & _
"WHERE ID=" & CInt(Right(rsdbo.Fields("Bayno"), 2))

Syntax error (missing operator) in query expression '98WHERE ID = 1'

All the fields in the query return the correct results in the immediate window.


Thanks

jpl

 

[randy700]

Syntax error (missing operator) in query expression '[red]98WHERE[/red] ID = 1'

Perhaps a missing SPACE?


Randy

 

[PHV]

[highlight] [/highlight]WHERE ID=" & CInt(Right(rsdbo.Fields("Bayno"), 2))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

I don't see where. Tried adding a space In "WHERE ID =" & CInt(Right(rsdbo.Fields("Bayno"), 2)) after the ID but get the same error.

jpl

 

[jpl458]

Tried Changing it to:
"UPDATE BayCounts " & _
"SET [Count]=" & rsdbo.Fields("StCount") - rsdbo.Fields("SumOfBalls") & _
"WHERE ID=" & CInt(Right(rsdbo.Fields("Bayno"), 2))

and get the same error

jpl

 

[dhookom]

jpl458,
I agree with the two who are attempting to help and would like you to post your current code.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Again, add the space before the WHERE.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

Putting the space before the WHERE worked. I thought I had a space in the previous line before the break character. In the

udq = _
"UPDATE BayCounts " & _
"SET [Count]=" & rsdbo.Fields("StCount") - rsdbo.Fields("SumOfBalls") & _
" WHERE ID=" & CInt(Right(rsdbo.Fields("Bayno"), 2))

Are there not now two spaces prior to the WHERE, one in the previous line and one directly before the WHERE? It's running and giving the proper results. Why the two spaces?

Thanks again

jpl

 

[dhookom]

I don't know where you have a space in the previous line. You might want to use some debugging to find your errors:

udq = _
"UPDATE BayCounts " & _
"SET [Count]=" & rsdbo.Fields("StCount") - rsdbo.Fields("SumOfBalls") & _
" WHERE ID=" & CInt(Right(rsdbo.Fields("Bayno"), 2))
Debug.Print udq

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

These

"SET = ...("SumOfBalls")[COLOR=black yellow] [/color]&[COLOR=black yellow] [/color]_

ARE NOT spaces in the SQL statement. They are a part of the VBA syntax. If you wanted a space there then you would need to explicitly put it in.

"SET = ... ("SumOfBalls")[red] & " " [/red]& _