Leading Zeros

[mattc66 via AccessMonster.com]

Hi All,

I need to add leading zeros. I can do it if it's always the same data.
However I have a little complicated issue.

I have data that needs to be 7 digit plus at times there may be 7 digits and
a dash with 2 digits.

This is how the data needs to appear:
0000045
0032456
0169256
0169256-01
0169277

This is what I am working with:
45
32456
169256
169256-01
169277

How would I format the query so that I always get 7 digits before the dash
and leading zeros on the items under?

 

[Guest]

Try

SELECT TableName.FieldName,
IIf(InStr([fieldname],"-"),Format(Left([FieldName],InStr([FieldName],"-")-1),"0000000")
& Mid([FieldName],InStr([FieldName],"-")),Format([FieldName],"0000000")) AS
NewFieldName
FROM TableName

 

[Tom Lake]

Hi All,

I need to add leading zeros. I can do it if it's always the same data.
However I have a little complicated issue.

I have data that needs to be 7 digit plus at times there may be 7 digits
and
a dash with 2 digits.

This is how the data needs to appear:
0000045
0032456
0169256
0169256-01
0169277

This is what I am working with:
45
32456
169256
169256-01
169277

How would I format the query so that I always get 7 digits before the dash
and leading zeros on the items under?

IIf(Instr([Number], "-")>0, Right("0000000000" & [Number], 10),
Right("0000000" & [Number], 7))

Tom Lake