query appends letter

L

Lapchien

I have a query that adds a letter C to the front of numbers in a field:

UPDATE TRTS_NEW SET TRTS_NEW.INV_NUMBER = "C" & [INV_NUMBER]
WHERE (((TRTS_NEW.TXN_TYPE)="P"));

However, the original field in the database was formatted for numbers, now
it's text and so sometimes, where the number started with 0 or 00 or 000
there is only 1, 2, 3 or 4 numbers in the string. I need it to always be 4
numbers, so 0001 or 0044 or 0123 or 1234. IS there any way to 'pad' to 4
digits (adding 0 as the padding number)?

Thanks,
Lap
 
G

Guest

format([Field1],"0000"
(this will only work with strings that have a numeric value

so
format("1","0000") = "0001

but
format("a","0000") = "a
 
M

Michel Walsh

Hi,

.... SET inv_Number = FORMAT( inv_number, '\C0000') WHERE ...


Hoping it may help,
Vanderghast, Access MVP
 
T

Technical Writer

You can also use:

UPDATE TRTS_NEW SET TRTS_NEW.INV_NUMBER = "C" &
(Left("0000",4-Len([INV_NUMBER] & "")) & [INV_NUMBER] )
WHERE (((TRTS_NEW.TXN_TYPE)="P"));

Brian
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top