Questions

MS Access 2000 Leading zeros

+
0 Votes
Locked

MS Access 2000 Leading zeros

jonb
Is there a way of adding and storing leading zeros to a number in a table field without them being entered?

ie; 7001 to be stored as 007001 and
35035 stored as 035035
  • +
    0 Votes
    Tony Hopkinson

    You'll need to make the column a char type if you want to store it, or you could just do it on output.

    select replicate('0',6 - DataLength(Convert(VarChar(6),[myinteger]))) + Convert(VarChar(6),[myinteger])
    from mytable
    where [myinteger] = 7001
    will give
    007001 as the answer

    Whether you do this on update or insert, or simply leave it for selecting, will depend mainly on convience I suspect.

  • +
    0 Votes
    Tony Hopkinson

    You'll need to make the column a char type if you want to store it, or you could just do it on output.

    select replicate('0',6 - DataLength(Convert(VarChar(6),[myinteger]))) + Convert(VarChar(6),[myinteger])
    from mytable
    where [myinteger] = 7001
    will give
    007001 as the answer

    Whether you do this on update or insert, or simply leave it for selecting, will depend mainly on convience I suspect.