Create a unique Customer ID

[mafhobb]

Hi.

So I have this field in a form/table called "CustomerID". This is to be an ID number for our customers that includes the first four letters of their last name (LastName field) followed by four digits.

This ID is created when a button is pressed in the form. After that, code needs to grab the first four letters of the last name (LastName field in the form and table) and then go to the Contacts table and see if there is already a CustomerID that starts with those four letters. If there is not, then the CustomerID will be those four letters followed by "0001". If there is, it needs to be those four letter followed by the next number sequence that still does not exist for those four letters.

For example:
First four letters of last name = zabo
Check to see if those four letters exist in CustomerID returns negative then
CustomerID assigned = zabo0001

Another example:
First four letters of last name = zabo
Check to see if those four letters exist in CustomerID returns zabo0001 and zabo002 then
CustomerID assigned = zabo0003

Does this make sense?

How would you go about doing this?

Thank you

mafhobb

 

[mafhobb]

The letters are easy enough, but no suggestions on how to get the digits?

 

[plog]

Actually, the letter's aren't that easy: What happens when Julie Smith marries and becomes Julie Jones? What is the ID for Shawn O'Connor?

With that said I would store this ID in 2 fields, one for the text, and one for the numbers. That way you can do use DMAX (http://www.techonthenet.com/access/functions/domain/dmax.php) to get the highest valued number for any four letters, add one to that and get the numeric part for your new ID.

 

[mafhobb]

Thanks for the thoughts.

I think understand what you are saying regarding the Dmax function to find the highest valued number, but I am not sure how that would help me because what I need is the highest number for those specific four letters, for example, I may have a zabo001, zabo002, zabo003 and also a john001 and john002. If a new john is entered it needs to go to john003, not john004.

Perhaps I am not quite understanding what you are telling me?

mafhobb

 

[plog]

My method would do that. Check out the DMAX function (http://www.techonthenet.com/access/f...omain/dmax.php). You can supply it with criteria, which means you can pass it 4 characters and it would return the highest value in the database for records with those characters, not just the highest value of all records.

 

[mafhobb]

I see. That link is great. I wonder if there is a way to do this without having the CustomerID split in two different fields. I have no idea about how many places that ID is used in. Perhaps I can have a table that only has those two fields (letters on one and digits on the other) to do the verification, but that once the ID is accepted then the two strings are joined into one and added to my current table as a single string?

mafhobb

 

[plog]

I wouldn't do it that way. Technically those pieces of data are discrete and should be stored as such.

 

[mafhobb]

Well, because that CustomerId can have several formats, including one that is all numbers, I decided to follow your advice but use a separate table to figure out the digit. It seems to work really well.

Thank you for your advice!

mafhobb