query appends letter

  • Thread starter Thread starter Lapchien
  • Start date Start date
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
 
format([Field1],"0000"
(this will only work with strings that have a numeric value

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

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

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


Hoping it may help,
Vanderghast, Access MVP
 
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
 
Back
Top