none
Combine a table field with other data RRS feed

  • 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

    Thursday, July 14, 2016 3:49 PM

Answers

  • 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

    • Marked as answer by Jessicasdd Thursday, July 14, 2016 6:42 PM
    Thursday, July 14, 2016 6:42 PM

All replies

  • 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

    Thursday, July 14, 2016 3:55 PM
  • 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

    Thursday, July 14, 2016 5:11 PM
  • 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

    • Marked as answer by Jessicasdd Thursday, July 14, 2016 6:42 PM
    Thursday, July 14, 2016 6:42 PM