Product Code

[Keith Ward]

Hi,

We have product ID codes generated as an autonumber field. However we want
to produce a catalogue with a code for reference but we want that code to be
say 8 characters long for all products. I would like them to have letters
and numbers in.

I was thinking of trying to convert the Product ID field to hexadecimal to
achieve this.

Whats the best way of automatically generating codes that are a set length
for all products? Also I would like to add two letters to the front of this
code to which will depict the supplier. These 2 letter codes are already
contained in a field.

Thanks

Keith

 

[John Vinson]

Hi,

We have product ID codes generated as an autonumber field. However we want
to produce a catalogue with a code for reference but we want that code to be
say 8 characters long for all products. I would like them to have letters
and numbers in.

I was thinking of trying to convert the Product ID field to hexadecimal to
achieve this.

Whats the best way of automatically generating codes that are a set length
for all products? Also I would like to add two letters to the front of this
code to which will depict the supplier. These 2 letter codes are already
contained in a field.

I would recommend NOT using the autonumber field type, at all.
Autonumbers are not appropriate for human-viewed values; they cannot
be edited, will always have gaps, and can become random (if you
replicate your database, for instance, the next two autonumber values
might be 1223814225 and -2016224953).

Instead, change the datatype from Autonumber to Long Integer. This
will preserve the existing values; you'll need to manually assign new
numbers, or get some of the readily available "custom counter" code to
do so automatically.

DO NOT include the two-byte supplier code in this field. Instead,
store it separately, as you are doing, and concatenate it for display
purposes:

ShowID: [SupplierCode] & Format([ProductID], "000000")


John W. Vinson[MVP]

 

[Keith Ward]

Hi,

Hi

I don't ever want to edit the auto number field because its the only way to
track the product as its link to the print catalogue content. We can get
products from a number of suppliers and at the moment the code shown in the
print catalogue is the supplier code with the supplier code prefix
concatenated to the front as you say.

Its just that the supplier codes can be very long and I just want a way to
auto generate a unique 8 character code for each product, that can be auto
generated whether that's to replace a code or adding in a new number.

Thanks

This is just it, with a few additions I will have cracked it.

Hi,

We have product ID codes generated as an autonumber field. However we want
to produce a catalogue with a code for reference but we want that code to be
say 8 characters long for all products. I would like them to have letters
and numbers in.

I was thinking of trying to convert the Product ID field to hexadecimal to
achieve this.

Whats the best way of automatically generating codes that are a set length
for all products? Also I would like to add two letters to the front of this
code to which will depict the supplier. These 2 letter codes are already
contained in a field.

I would recommend NOT using the autonumber field type, at all.
Autonumbers are not appropriate for human-viewed values; they cannot
be edited, will always have gaps, and can become random (if you
replicate your database, for instance, the next two autonumber values
might be 1223814225 and -2016224953).

Instead, change the datatype from Autonumber to Long Integer. This
will preserve the existing values; you'll need to manually assign new
numbers, or get some of the readily available "custom counter" code to
do so automatically.

DO NOT include the two-byte supplier code in this field. Instead,
store it separately, as you are doing, and concatenate it for display
purposes:

ShowID: [SupplierCode] & Format([ProductID], "000000")


John W. Vinson[MVP]

 

[John Vinson]

Hi,

Hi

I don't ever want to edit the auto number field because its the only way to
track the product as its link to the print catalogue content. We can get
products from a number of suppliers and at the moment the code shown in the
print catalogue is the supplier code with the supplier code prefix
concatenated to the front as you say.

Its just that the supplier codes can be very long and I just want a way to
auto generate a unique 8 character code for each product, that can be auto
generated whether that's to replace a code or adding in a new number.

I'd still suggest avoiding Access' Autonumber field type. It can
become random, as I said, and you will not have the opportunity to
reuse the same numeric value with different letter codes (e.g. if you
want to be able to use AB000001 and XY000001, then Autonumber is not
for you!)

However, it's pretty easy to write some simple VBA code to
automatically assign numbers. You'll have to explain how to get from a
"very long" supplier code to a two-character code that is distinctive
and usable, and how you want the numbers to be generated. *DO* you
want to reuse the same number for different suppliers, or do you want
the numbers to be unique in their own right?


John W. Vinson[MVP]