Using a query to generate Stock Numbers

[Guest]

When adding a record I would like to combine part of two fields (first 3
letters of the Province field and the last 5 digits of the Autonumber primary
key) to form a unique "Stock Number". I used an append query with a
concatinated expression. Every thing works fine except the leading zeros do
no hold their place. e.g. "KAS5" is generated by the query but it shoud be
"KAS00005". How do I get the leading zeros to stay? Dose it have anything
to do with the autonumber being a number and the Stock Number being text?

 

[OfficeDev18 via AccessMonster.com]

I assume you're formatting the Stock Number right in your Append query. In
that case, try this.

StockNumber: Left(Province, 3) & Format(PrimaryKey, "####0")

Look up the Format() function in the Help file, and be sure to read all the
examples to understand the "##" part of it.

HTH

 

[Guest]

Yep.
When you concatenate do this --
[Province] & right("00000" & [YourPrimaryKeyField],5)

 

[Guest]

Outsanding!! Thank you both. I will be back if it doesn't work but I'm sure
it will.
--
Question Mark

Yep.
When you concatenate do this --
[Province] & right("00000" & [YourPrimaryKeyField],5)

When adding a record I would like to combine part of two fields (first 3
letters of the Province field and the last 5 digits of the Autonumber primary
key) to form a unique "Stock Number". I used an append query with a
concatinated expression. Every thing works fine except the leading zeros do
no hold their place. e.g. "KAS5" is generated by the query but it shoud be
"KAS00005". How do I get the leading zeros to stay? Dose it have anything
to do with the autonumber being a number and the Stock Number being text?

 

[Guest]

You mention that the numeric part of the stock number is generated by an
autonumber. Be aware that autonumbers are not guaranteed to be consecutive,
and allow the entering of missing numbers, resulting in a new record that has
a smaller number than the max.