Character manipulation in sql query

I don't know if this can be done or not.
I have a field in a query that always starts with a letter, and is followed by 1 to 7 numbers.  I need to insert 0's after the letter so the result is 7 characters long.

Examples:
D123  --> D000123
E01 --> E000001
rrhandle8Asked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
OOPS ... 7 total


Left("D123",1) & Format(Mid("D123",2),"000000")

UPDATE Table1 SET Table1.FIELD1 = Left([FIELD1],1) & Format(Mid([FIELD1],2),"000000");
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Example:

Left("D123",1) & Format(Mid("D123",2),"0000000")

returns
D0000123

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Update query:

UPDATE Table1 SET Table1.FIELD1 = Left([FIELD1],1) & Format(Mid([FIELD1],2),"0000000");


mx
0
 
rrhandle8Author Commented:
Perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.