Error running SQL update query in VBA

[David S]

Hi
I keep getting an error "Compile error. Expected: End of Statement" when using the following within VBA?

DoCmd.RunSQL UPDATE [tblBACS Charge File report] SET [tblBACS Charge File report].Field4 = "0"+[Field4] WHERE ((Len([Field4])<"4"));

I am trying to update a table where a text field [Field4] is only 3 characters long and should be 4 characters with a leading zero ["0"]? e.g. "106" should be "0106".

The above SQL was created [by the system] from an update query that works perfectly as native and also if run as SQL within a macro. It will just not run in VBA?

I am a bit of a novice so any help would be most welcome.

NB Am using Access2010.
Thanks
David

 

[pbaldy]

The entire string needs to be enclosed in double quotes, which also means the existing double quotes need to be changed to singles.

 

[namliam]

Is Field4 indeed a text field? or a number field? In the second case adding a leading zero is useless.
The function LEN returns a number value, not a text value.

In accoordance with above and Paul's comment:

DoCmd.RunSQL "UPDATE [tblBACS Charge File report] SET [tblBACS Charge File report].Field4 = ""0""+[Field4] WHERE ((Len([Field4])<4));"

 

[David S]

Hi guys - Result!
Many thanks indeed to you both - the update with quotes as per 'namlian' post is spot on. I doubt I would have got there on my own.
Very kind regards
David

NB As stated in my original post, the 'Field4' is a text field and the syntax appears to work perfectly [for me].

 

[jdraw]

David,

You may want to consider using a naming convention that does NOT allow spaces in field and object names. It will save you from syntax errors and related debugging over time.

Rainlover who participates on this forum has a naming convention that is often referenced.