Need query to add a leading zero to text field 2

[PAWelch]

For one-time use only. I have a text field in a table that contains ID numbers. The client wants all IDs to be 6 characters though 15 are allowed. At present, some are 6 and others have only 5. So leading zeros must be added to those that are only 5 characters.

I've seen some tips on doing code in AfterUpdate events. But I'm just a simple user at present and don't have a clue as to how to code. I thought that an update query would solve this to update the table itself; yet I am unable to get an effective formula going. Is there an update query that can accomplish this?

Thank you.

 

[AlexCuse]

Something like this:

right("0" & CStr(myColumn), 6)

ought to do the trick. Make sure you are storing the ID #'s as text, because numeric data types will not show leading zeroes.

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[traingamer]

right("0" & [yourfield], 6)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

rats, late again.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PAWelch]

Please excuse my ignorance but how do I make that a query? In SQL view (MSAccess 2003), I entered:

SELECT PDRT.EvaltrID
FROM PDRT
right("0" & CStr[PDRT.EvaltrID], 6);

It comes back with a Syntax error in JOIN problem.

 

[AlexCuse]

SELECT PDRT.EvaltrID
[b], right("0" & CStr[PDRT.EvaltrID], 6) as MyExpr[/b]
FROM PDRT

Hope it helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[traingamer]

Or:

UPDATE PDRT SET PDRT.EvaltrID = right("0" & [PDRT.EvaltrID], 6);

as an update query.

or include the parens in Alex's version right("0" & CStr([PDRT.EvaltrID]), 6)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PAWelch]

Thanks, fellas! I used the UPDATE formula and it worked. I'm going to add the others to my book and try them out on my table copies. Geez, I need to learn so much more!!!

Thank you!