leading zeros disappear

[helenpostle]

I am joining two fields in a query, JobNo which is a text field, and SampleNo which is a number field. SampleNo has to be 4 numbers so if the sample number is 1 it becomes 0001 and if it is 10 it becomes 0010. The sampleno works fine but when I join it to the JobNo field, the leading zeros disappear so if the JobNo is abcd/1234 and the SampleNo is 0001 the joined field becomes abcd/1234/1 instead of abcd/1234/0001. How can I make the zeros stay in?

Helen

 

[Pat Hartman]

Use the Format() function.

JobNo & "/" & Format(SampNo,"0000")

 

[David R]

Also, if the field is full of numbers but you'll never be performing any calculations on it, you can make it a text field. Make sure you put an input mask of 0000 to restrict it to 4 numbers.

HTH,
David R