Combine a table field with other data

Question

I have a table that has a list of invoice numbers, along with a bunch of other data. The invoice numbers are 6 characters. I need them to be at least 15 characters.

In one column the invoice number is 012345. I need it to be 000000000012345.

How can I get the leading zeros in without typing them into each cell?

Thanks,

Jessica

Answer 1

A leading zero? What is the data type of this invoice number? Look it up in table design.

Generally speaking you would use the Format function in a query to add the leading zeros. They are only for human consumption (although it's debatable that 10 leading zeros really are good for humans).

---

-Tom. Microsoft Access MVP

Answer 2

The data type is Text. We upgraded our ERP software and the field size for the invoice number went from 6 to 20. The rule for it says if it is alpha-numeric then it needs up to 20 characters. If it is only numeric then it needs up to 15 characters. The invoice numbers in all of the ERP software's tables now read as 000000000012345. Any table we made prior to the upgrade that has an invoice number in it only has 012345. I need to add the other 9 zeroes to the beginning of the invoice numbers so that the tables will relate properly.

Thanks,

Jessica

Answer 3

I found a way to do it...

UPDATE Monthly_Commissions SET Monthly_Commissions.Com_Invno = "000000000" & Com_Invno
WHERE (((Len([Com_Invno]))=6));

It worked. Changed every invoice number to have 15 characters with all the zeros leading.

Thanks,

Jessica