syntax on update query-WHERE statement

[starrcruise]

I give up. Two questions (I'm using Access 2007). One, would you please give me the correct syntax for the below UPDATE code. I am getting a "missing operator" on the WHERE statement. Criteria fields are both text.
("UPDATE [policy changes] SET [Policy Changes].[Legislation ID]=" & Me.SourceID _
& "WHERE [Policy Changes].[ChangeControlNo]='" & Me.cboBulletinNumber & "'")

Secondly - I wanted to make the whole update code a string so I could just put: DoCmd.RunSql (sqlPol)

But I am not getting the correct syntax on that as in:
sqlPol = "UPDATE [Policy Changes] SET " & _
"Policy_Changes.Legislation_ID=" & Me.SourceID & _
"WHERE [Policy Changes].[ChangeControlNo] ='" & Me.cboBulletinNumber & "'"";"

As you can see, I'm not the brightest light bulb on this stuff. Thank you.

 

[spikepl]

insert

debug.print sqlPol

and see what the output looks like in the Immediate Window

 

[Kiwiman]

Howzit

you may be missing a space before "WHERE"

Try

("UPDATE [policy changes] SET [Policy Changes].[Legislation ID]=" & Me.SourceID _
& " WHERE [Policy Changes].[ChangeControlNo]='" & Me.cboBulletinNumber & "'")

Also if cbobulletinnumber is a number you will want

("UPDATE [policy changes] SET [Policy Changes].[Legislation ID]=" & Me.SourceID _
& " WHERE [Policy Changes].[ChangeControlNo]=" & Me.cboBulletinNumber)

 

[starrcruise]

Here is the error code I am getting when I run the sqlPol update string (2nd code)

the number (427) in front of the Where statement in the error code is the Me.sourceID number at the end of the SET statement. The POL# at the end of the WHERE statement is correct also. I'm just not getting the code right between the Me.SourceID and Where statement.

Here is the error code
Error Number 3075. Syntax error (missing operator) in query expression '427WHERE[Policy Changes].[ChangeControlNo] =POL0220120256

Geez, it feels like its right there, but I just can't get my head wrapped around it.

 

[starrcruise]

KiwiMan: You were right on with the space before W in both codes Paleez, all the code I looked at I did not see that. Secondly, any guess on the second code. I got the number removed (427) once you told me about the space, but I am still getting this 3075 error. I am thinking I need some kind of parens or my quotes are not the right numbers in the Where statement. Also, both fields in the WHERE are text, not numbers. Now it just says Error Code 3075. Syntax error in [Policy Changes]. [ChangeControlNo]='POL0220120256"';'

With your correction, I got the first code working. Maybe I should just quit. But I really would like the proper syntax for making the whole thing a string. Thank you very very much for your previous help again.

 

[Kiwiman]

Howzit

Never give up - By hook or by crook you'll get there.

Which is correct. You have UPDATE [Policy Changes] a space in the table name but then you have SET Policy_Changes.Legislation_ID in the SET part - no space but an underscore. They need to be the same - I would recommend not having spaces in table \ form \ field names etc - it just makes coding a little more difficult. Based on the table name try this



Try this

sqlPol = "UPDATE [Policy Changes] "
sqlPol = sqlPol  & "SET [Policy Changes].[Legislation_ID]=" & Me.SourceID
sqlPol = sqlPol  & " WHERE [Policy Changes].[ChangeControlNo] ='" & Me.cboBulletinNumber & "';"

debug.print sqlPol

 

[starrcruise]

Good morning (for me) KiwiMan! You rock. Here's what happened: You were absolutely right about the Set statement the table name being not consistent. But before I fixed that (always experimenting), I realized that you had inadvertently answered the entire question, as you also wrote your string for my sql as sqlPol = sqlPol & " rather than how I was doing it (I coded mine like a message box string). So I went in and fixed my code as you did without changing the table name in SET. It eliminated the 3075 error, but of course, came up asking me what the heck Policy_Changes.Legislation_ID was. So then I went in and fixed it as you said. Bingo. More importantly, I appreciate your support.

Oh yeah, HATE spaces in the table names (there is even one named with "/" in it), but I inherited the whole mess and just didn't want to change a bunch of code (wish I had now).