Selecting Spefic Data

[Guest]

Hi

Not sure if this is in the right place but hope someone can help.

I am working with an existing database that i havent design or had nothing
to do with till now. What i need to do is to select the 2nd, 3rd and 5th
letter of our clients surnames and then the 2nd, 3rd letter of there
christion name. This is to creat a unique ID for each client.

The client names are all in one field at the moment. Is it possible for
this to be done and the unique id be placed in an additional column i have
added to the end of the clients information table.

Hope someone can help

PS There isnt to many clients at this stage so if its easier i can split the
names into first name and surnames.

 

[Wayne Morgan]

It is possible to do if there are that may letters in their name (5 in the
surname and 3 in the first name). However, for this to be a unique ID would
require it to be unique. There is no guarantee that this will result in
unique values.

As far as the client's names all being in one field, I would recommend
breaking them into two fields, one for the first name and one for the last
name. You can always concatenate them together for display purposes in the
reports.

Once you have split the names into two separate fields so that there is no
confusion if one of the names has a space in it, then yes, you could run an
Update Query that would add the value you mention to a new field in the
table.

 

[Steve Schapel]

Andrew,

It would certainly be easier and preferable to have separate fields for
first name and surname.

You could create this ID by using string functions. For example...
Mid([Surname],2,2) & Mid([Surname],5,1) & Mid([FirstName],2,2)

However, you will need to work out what you want to do in the case of
exceptions. For example, if the first name is Jo, there is no 3rd
letter, so what then? Or if the surname is Da Vinci, the 3rd character
is a space, so what then? Or if the surname is O'Brien, the 2nd letter
is an apostrophe, so what then? If you want to handle these situations,
it will probalby be best to write a user-defined function to compile the ID.

Either that, or think of an easier way to get a unique ID for the
clients.

 

[Guest]

where do i enter this string information.

Exceptions would be if there is no letter for a number 2 to be used instead

Andrew,

It would certainly be easier and preferable to have separate fields for
first name and surname.

You could create this ID by using string functions. For example...
Mid([Surname],2,2) & Mid([Surname],5,1) & Mid([FirstName],2,2)

However, you will need to work out what you want to do in the case of
exceptions. For example, if the first name is Jo, there is no 3rd
letter, so what then? Or if the surname is Da Vinci, the 3rd character
is a space, so what then? Or if the surname is O'Brien, the 2nd letter
is an apostrophe, so what then? If you want to handle these situations,
it will probalby be best to write a user-defined function to compile the ID.

Either that, or think of an easier way to get a unique ID for the
clients.

--
Steve Schapel, Microsoft Access MVP

Hi

Not sure if this is in the right place but hope someone can help.

I am working with an existing database that i havent design or had nothing
to do with till now. What i need to do is to select the 2nd, 3rd and 5th
letter of our clients surnames and then the 2nd, 3rd letter of there
christion name. This is to creat a unique ID for each client.

The client names are all in one field at the moment. Is it possible for
this to be done and the unique id be placed in an additional column i have
added to the end of the clients information table.

Hope someone can help

PS There isnt to many clients at this stage so if its easier i can split the
names into first name and surnames.

 

[Steve Schapel]

Andrew,

For the existing records, you could make and run an Update Query, and
the equivalent of the concatenation expression I gave before would be
put in the Update To row of the ID field in the query design grid.

For new records, I guess you will want the ID to be calculated and
inserted at the time of a new record being created. Probably the Before
Update event of the form would be a good event to use, or else the After
Update event of the FirstName and Surname controls on the form.

Here is some skeleton "air code" for a function...

Public Function MakeID(SName As String, FName As String) As String
Dim strName As String
Dim strID As String
strName = SName
strName = Replace(strName," ","2")
strName = Replace(strName,"-","2")
strName = Replace(strName,"'","2")
If Len(strName) > 4 Then
' proceed
Else
strName = strName & "2222"
End If
strID = Mid(strName,2,2) & Mid(strName,5,1)
strName = FName
strName = Replace(strName," ","2")
strName = Replace(strName,"-","2")
strName = Replace(strName,"'","2")
If Len(strName) > 2 Then
' proceed
Else
strName = strName & "22"
End If
strID = strID & Mid(strName,2,2)
' handle possibility that ID already exists
Do
If DCount("*","Clents","[ID]='" & strID & "'") = 0 Then
Exit Do
Else
strID = Left(strID, 4) & Chr(Asc(Right(strID, 1)) + 1)
End If
Loop
MakeID = strID
End Function

Something like that, anyway!

Then, on your form, you would just use code like this...
Me.ID = MakeID(Me.Surname, Me.Firstname)