Help with Code Please

[Guest]

I have through two sepate questions got some answers which have helped with
my problem

i have a table sellers and need to assign a seller codde to each made up of
parts of surname and forname and two digits.

the code below works so long as they are not O'Brien or O'Donnell or othe O'
surnames
Public Sub MakeRef()
Dim strWhere As String
Dim ingnextnum As Long

Dim s_name2 As String
Dim F_name2 As String

If Len(Nz(Me!F_Name, "")) < 2 Then
'blank first name.... don't do anything
Exit Sub
End If
If Len(Nz(Me!S_Name, "")) < 2 Then
'blank surname....don't do anything
Exit Sub
End If
s_name2 = Left(Me!S_Name, 2)
F_name2 = Left(me!F_name, 2)

strWhere = "(S_name like '" & s_name2 & "*')" & _
" and (F_name like '" & F_name2 & "*')"


If IsNull(Me!ID) = False Then
' this not a new reocrd, so we must exclude this reocrd
strWhere = strWhere & " and (id <> " & Me!ID & ")"
End If

lngNextNum = DCount("*", "clients", strWhere) + 1

Me.C_ref = s_name2 & F_name2 & Format(lngNextNum, "00")




End Sub
but if surname is O' gives error code
run time error 3075
Synatx error(missing operator) in query expression '(S_Name like'O''*') and
(F_Name like 'je*') and (id<>2)'

I tried putting in the following code

if mid(S_Name,2,1)="'" then s_name2 =mid(S_Name,1,1)&mid(S_Name,3,1)
else
s_name2=left(me!sname,2)

this gets round the apostrophe but won't let two sellers with the same
initials eg conor o'Brien and Colm O'Brien as it doesn't increment the 01
part of key.

where am i going wrong

colm

 

[Guest]

The problem removing the apostrophe may be in where you are doing it. See
below where I put it. I used the Replace Function, it is easier to read.

I have through two sepate questions got some answers which have helped with
my problem

i have a table sellers and need to assign a seller codde to each made up of
parts of surname and forname and two digits.

the code below works so long as they are not O'Brien or O'Donnell or othe O'
surnames
Public Sub MakeRef()
Dim strWhere As String
Dim ingnextnum As Long

Dim s_name2 As String
Dim F_name2 As String

If Len(Nz(Me!F_Name, "")) < 2 Then
'blank first name.... don't do anything
Exit Sub
End If
If Len(Nz(Me!S_Name, "")) < 2 Then
'blank surname....don't do anything
Exit Sub
End If

s_name2 = Left(Replace(Me!S_Name,"'",""),2)

 

[Rick B]

As posted many many times recently, you should not use part of a name in
your key field. What happens when someone gets married? or divorced? Or
adopted? Or simply chooses to change their name?

 

[Guest]

this still causes the problem with conor o'brien and colm o'brien creating
duplicate values in primary key

 

[Guest]

I will second what Rick said. You should not be using names or portions of
names as primary keys or as any part of the primary keys. I'm not sure what
you mean by "the 01 part of the key", but many names are going to give you
the same problem with duplicate values.