Query expression removing leading zeros

[Design by Sue]

I have a database to keep track of part numbers that are 5 digits and a
suffix that can be up to three digits. My table are set up with a format of
00000 for the 5 ditig part number. I have created reports that show the two
fields concatenated as part number - suffix (ex: 00231-1, 00231-50) The
problem I am having is the expression (Expr1: PartSuffixTbl!PartNumber & "-"
& Suffix) is removing the leading zeros. The query shows the correct format
for the partnumber field, but when the expression field shows, for my example
shown, 231-1, 231-50. How do I keep the leading zeros in the expression.
If you can help, please tell me where the format coding goes (I've tried on
the properties of the text box the report, but that didn't work)

Thanks in advance for any help.

 

[Design by Sue]

Never mind - I found it through other posts and a bit of mental exercise!

For others looking for the answer, mine was

Expr1: Format(PartSuffixTbl!PartNumber,"00000") & "-" & Suffix

in the query instead of the Expr1 I had.

Thanks
Sue

 

[John Spencer]

Of course the better solution would be to store the part numbers as text
strings. Then the leading zeroes would always be there and you would not
need to worry about formatting the Part Number (Part identifier) to force
leading zeroes.

I make little sense to store this data in a number field. You are not going
to do any math operations on it.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Design by Sue]

Good point - but if I remember the reason for making it a number was to avoid
having the user input letters in the part number field. (I think that was
the reason) Anyway, thanks for your input - I got it working this way and
really don't want to back track through all of what I have done to make sure
I catch all of the reprocussions (sp?) changing this might have.

Sue

 

[Tom Lake]

Good point - but if I remember the reason for making it a number was to
avoid
having the user input letters in the part number field. (I think that was
the reason) Anyway, thanks for your input - I got it working this way and
really don't want to back track through all of what I have done to make
sure
I catch all of the reprocussions (sp?) changing this might have.

Sue

You can use an input mask that only allows digits in a text field.

Tom Lake