Combining data in 2 fields to create a new field

[leaners]

Hi,

I have two fields. One is called firstname the other is called surname.

I want to take the first 3 letters of the surname and the first 3 letters of the firstname and combine them to create the primary key (called CustomerID).

So basically, the user enters his firstname and surname, then the CustomerID is filled automatically using 3 letters from the surname and 3 letters from the firstname.

E.g.
CustomerID: JONSAM
Firstname: Samuel
Surname Jones

The CustomerID fills automatically using the firstname and surname fields.

But what if the firstname is Jo and the surname is Li. Then it should take 2 letters from the surname and 2 from the firstname. What happens if there are two samuel jones in the database? ie someone with the same name? Then a unique number should be attatched to the primary key.

This way, on a search form, the user types the primary key, instead of the surname and firstname, and has more chance of remembering it.

Any help would be appreciated.

 

[Remou]

You can use Left to get the letters and, as far as I recall, the usual thing is to use X as a filler and then tack a number on the end:

sn = "Li"
fn = "Jo"
Fill = "XXX"
i = 1
k = Left(Trim(sn) & Fill, 3) & Left(Trim(fn) & Fill, 3)
Do While True
k1 = k & Format(i, "000")
Debug.Print k1
    If Not IsNull(DLookup("ID", "tblTable", "ID='" & k1 & "'")) Then
        i = i + 1
    Else
        Exit Do
    End If
Loop