Creating a Key Field Value from other values

[notoca]

Hi

I have a key field in a dat entry form that is currently entered manually by the user. The key has the format XXXXXX-000 that is the first 6 characters are letters and the last 3 characters are a number eg 001, 002, etc.

The first part of the key value is made up of the first 6 letters (ignoring spaces) of the value in another field. For example, values in this field could be: "University of New South Wales", "University of WA", Tap Oil, etc. As you can see taking the first 6 characters of these values can result in duplication eg UNIVER. So inorder to make the value unique I thought that each like value should be sequentially number. In other words the three companies above would end up with key values as:
UNIVER-001 - University of New South Wales
UNIVER-002 - University of WA
TAPOIL-001 - Tap Oil

I suspect that the first part will involve a sting function to pick up the characters and get rid of the spaces. If any one can help it would be greatly appreciated.

Russell

 

[bat17]

Why make hard work for yourself? Just use an Autonumber which will give everone a unique number automatcaly rather than using a contrived number.
Your number will take a bit of managing because first you have to construct your letter code (presumably padding out names of less than 6 letters) then checking to see if that letter combination has been used before and if it has what is the last serial number attached.

Peter