vba sql update statement - can't get the format() syntax right

[rincewind_wizzard]

Hi all,

I'm trying to run the following in a module, but it doesn't like the 0000000 (I get expected: end of statement). The employee number field is text and I need to pad it with leading zeros.

'Tidy up the pay number in the tax and NI table - format to 7 places
strsql = "UPDATE tblIMPORT_NIable_Taxable_Earnings SET tblIMPORT_NIable_Taxable_Earnings.[Employee number] = Format([Employee number],"0000000");"

DoCmd.RunSQL strsql


Yours,
Paul

 

[theDBguy]

You could try:

strsql = "UPDATE tblIMPORT_NIable_Taxable_Earnings SET tblIMPORT_NIable_Taxable_Earnings.[Employee number] = '" & Format([Employee number],"0000000") & "';"

Hope that helps...

 

[cheekybuddha]

Out of curiosity, what datatype is field [Employee number] ?

 

[theDBguy]

You could try:

strsql = "UPDATE tblIMPORT_NIable_Taxable_Earnings SET tblIMPORT_NIable_Taxable_Earnings.[Employee number] = '" & Format([Employee number],"0000000") & "';"

Hope that helps...

On second thought, maybe more like this?

strsql = "UPDATE tblIMPORT_NIable_Taxable_Earnings SET tblIMPORT_NIable_Taxable_Earnings.[Employee number] = Format([Employee number],""0000000"");"

 

[rincewind_wizzard]

Out of curiosity, what datatype is field [Employee number] ?

Text

 

[rincewind_wizzard]

On second thought, maybe more like this?

strsql = "UPDATE tblIMPORT_NIable_Taxable_Earnings SET tblIMPORT_NIable_Taxable_Earnings.[Employee number] = Format([Employee number],""0000000"");"

Wonderful. Works fine. I thought I'd tried the extra quotes, must be imagining it.
Thanks

 

[Gasman]

I'd be interested to know if the first option worked?
The one with the concatenation?

 

[cheekybuddha]

Both versions in DBG's post #4 should work for you.

 

[theDBguy]

Wonderful. Works fine. I thought I'd tried the extra quotes, must be imagining it.
Thanks

Glad to hear you got it sorted out. Good luck with your project.