423,680 Members | 2,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,680 IT Pros & Developers. It's quick & easy.

Update Query

P: 14
I have a simple update query that should apply numbers to a text field, but it keeps dropping the leading zero. Should be 054846, but ends up as 54846. Can't figure out what I'm missing.

Expand|Select|Wrap|Line Numbers
  1. Dim strsql As String
  2. strsql = "UPDATE [Action]" _
  3.        & "SET [Action] = (" & "054846" & ")"
  4.  
  5. DoCmd.RunSQL strsql
I can't afford to pull anymore hair out, appreciate any help. Thanks.
Feb 17 '16 #1

✓ answered by Rabbit

Actually, I believe the situation is the reverse. It sounds like the field in the table is defined as a string. But the SQL you have has the number as an integer because you didn't surround the value with single quotes. You will see the issue if you output the SQL string you're trying to run.

Share this Question
Share on Google+
3 Replies


100+
P: 299
This is because your column is an integer column. 054846 is a string, 54846 is an integer. To keep the leading zero(s), you would have to change the column type to char (nchar, nvarchar, etc.). If this is not an option and the string is strictly for client-side application viewing, you could always format the string at the application layer.
Feb 18 '16 #2

Rabbit
Expert Mod 10K+
P: 12,272
Actually, I believe the situation is the reverse. It sounds like the field in the table is defined as a string. But the SQL you have has the number as an integer because you didn't surround the value with single quotes. You will see the issue if you output the SQL string you're trying to run.
Feb 18 '16 #3

P: 14
Thanks for the help, I forgot to post the response some time ago.
Dec 9 '16 #4

Post your reply

Sign in to post your reply or Sign up for a free account.