Adding zero's to text field 1

[WingandaPrayer]

Hello

I have a text field that shows a house number e.g.

23A
123A
27
1

My question is how do I change the above to
023A
123A
0027
0001
without having to manually add th zero's

Thanks in advance

David

 

[JoeMiller]

You can't use a format because of the house numbers with text in them (123A). You'll have to add 0's after finding out the length and such.

Joe Miller
joe.miller@flotech.net

 

[WingandaPrayer]

Joe,

Thanks for the reply but I'm not sure what you mean.

You'll have to add 0's after finding out the length and such.

At the moment I'm using an update query.....it's a bit long winded though typing 1A to read 001A etc

Thanks

David

 

[tlbroadbent]

Here is a simple UPDATE query that should work.

UPDATE TestTbl
SET TestTbl.TextCol = Right("000" & [TextCol],4)
WHERE Len([textcol])<4;
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[WingandaPrayer]

Thanks Terry,

that did it.


David