Question Insert a Character to a Field Data

fblittle

New member
Local time
Today, 14:48
Joined
Jan 22, 2012
Messages
7
My database has a field called "CallNumber", and the data is formatted like this: N140040 It is limited to 7 characters with the first character as a Letter and the rest all numbers. When I created my database I had the field limited to 6 characters with the same format, example: N78345 . When I ran out of numbers I expanded the field to 7 characters still with the first character as a Letter etc., but now when I sort the old fields that have 6 characters go to the top of the list. They are older and should be at the botton of the list.

What I want to do is to is add a "0" between the Letter character and the the first number character. I tried using find and replace, but could not find a good method to add the "0" without changing the rest of the field characters.

This may be a simple task but right now I can't figure a good way to accomplish this task. Your help would simplify my task.
 
Howzit

Try this - remember to take a back up first

Code:
UPDATE Yourtable SET yourtable.yourfield= Left([yourtable]![yourfield],1) & "0" & Right([yourtable]![yourfield],5)
WHERE ((Len([yourtable]![yourfield])="6"));
 
Thanks, Really Appreciate this it will save me a lot of time.
 
In the first place you shouldn't be storing the letter "N" as part of your field and you shouldn't need to update the field to fill it up with 0s using an UPDATE statement. If you create your field as a Number data type you can pad it with "N" and zeros using the Format property.

For example you could put A000000 in the Format property of the Number field.
 
Thanks for the tips. I created this database about 5 years ago so I am stuck with it now unless I do something major to mod it.

There is a possibility that in the future the people that issue this call number will up the letter to M or some other letter, But today the letter "N" stands for North America, indicating that the call originated in North America.
 
You can pad it with whatever you want, "N", "M"... etc. The fact is a Number will function better as an Index than a Text type.

Worth bearing in mind in the future.
 
Thanks, If I eventually have both Letter codes in my database table how will I be able to attach the letter code to the field without creating another field with just the letter code?
 
You will obviously need to create another table with just the letter codes. Thsn you can concatenate that letter code to the formatted numeric ID when you need to.
 
Howzit

Try this - remember to take a back up first

Code:
UPDATE Yourtable SET yourtable.yourfield= Left([yourtable]![yourfield],1) & "0" & Right([yourtable]![yourfield],5)
WHERE ((Len([yourtable]![yourfield])="6"));


I used your SQL and updated my tables. Work like a Charm. Thanks, saved me hours of manual editing.
 

Users who are viewing this thread

Back
Top Bottom