I need to create a sequential number for each customer and store them in the same table. For example:
PBM0001
PKZ0001
PBM0002
PKZ0002
PKZ0003
So, the number is sequential within each customer
The number has 3 parts:
1-the prefix will always be the letter "P" then,
2-the next two letters is the customers unique abbreviation then,
3- comes the sequential number padded to four decimal places.
The table will also store the unique CustomerID.
I'm not sure how best to create and store the number. I thought that I would do some sort of Dlookup or DMax of the sequential number based on the customer ID but if I store the whole string then I don't think that I can do the DMax on it because of the letters. So, do I store the parts in seperate fields? And then will the padding of zeros mess up the DMax?
PBM0001
PKZ0001
PBM0002
PKZ0002
PKZ0003
So, the number is sequential within each customer
The number has 3 parts:
1-the prefix will always be the letter "P" then,
2-the next two letters is the customers unique abbreviation then,
3- comes the sequential number padded to four decimal places.
The table will also store the unique CustomerID.
I'm not sure how best to create and store the number. I thought that I would do some sort of Dlookup or DMax of the sequential number based on the customer ID but if I store the whole string then I don't think that I can do the DMax on it because of the letters. So, do I store the parts in seperate fields? And then will the padding of zeros mess up the DMax?