Create unique record id

[David27]

I am trying to find a way to have Access 2000 create a unique record ID when record is updated based on customer name. ID would consist of first five characters of last name plus first three characters of first name plus middle initial of customer. Example: SMITHBILA. If ID length is less than nine characters filler characters would be added. If a duplicate value existed an error message would be displayed.

David

 

[missinglinq]

I'm assuming that you have your first name, middle initial, and last name in separate fields. If so, first do something like this:
UniqueID=Left(LastName,5) & Left(FirstName,3) & MiddleInitial

On "BILL A SMITH" this will get you your SMITHBILA

To check the length of the UniqueID and add a padding (in this example an asterisk):

If Len(UniqueID) < 9 then
UniqueID= UniqueID & String(9 - Len(UniqueID),&quot;*&quot
End IF

&quot;BILL A NYE&quot; would get you &quot;NYEBILA**&quot;

I think this'll do what you need to have done (although I don't have Access available right now to test it)

The Missinglinq


&quot;It's got to be the going,
not the getting there that's good!&quot;
-Harry Chapin