By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,849 Members | 2,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

How to concatenate an alphabet to a number.

P: 83
I have a number field. numServiceNo. It has 7 digits maximum. I want to add an alphabet to this field on the left hand side.

For example if the number is 8014342 I want to add A to the right and make it

What function do I use

May 1 '10 #1
Share this Question
Share on Google+
3 Replies

Jim Doherty
Expert 100+
P: 897

'A' to the right = 8014342A
'A' to the left = A8014342

String Functions (documented in access help) will give you an idea. Given you say you have a numeric field you will not be table storing the number with the alpha character obviously and I take it you are building your STRING on the fly something like the following code statement that converts the numeric value to a string and prefixes it with a concatenated 'A'

strServiceNo= "A" & Cstr([numServiceNo])

I will not explain IF logic that encompases the rest of the alphabet you have a strategy for that I guess
May 1 '10 #2

P: 83
Hi Jim,
Thanks a lot. I want to permanently add an alphabet to the number string and store it. The number numServiceNo is a text field.

Is there a query to update the number to a text?

Thanks and cheers
May 1 '10 #3

Jim Doherty
Expert 100+
P: 897
If you are simply wanting to prefix the letter 'A' to the field value for ALL rows in the single column text field of your database then use an UPDATE query.

The SQL syntax for this being the following (which you can simply paste into the Access SQL window and run it) where YourTableName = the actual name of your table

Expand|Select|Wrap|Line Numbers
  1. UPDATE YourTableName SET YourTableName.numServiceNo = "A" & CStr([numServiceNo]);
You talk about the alphabet but again I do not know your strategy or what logic sequence you might want to employ for dealing with this. That is a matter for you to decide upon and frame your question accordingly, if indeed it is required at all for your purposes.
May 1 '10 #4

Post your reply

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