Help with comparison

[Guest]

I have a database in access97. In the database there is a form where the user
takes names of clients as they come. I want to check after the user enters
the first and last name if the name exist in the data base, if it does it
should alert the data entry person. I am using the following code which all
of the sudden stoped functioning the way it should, i.e. there are similar
name that the code missed:

start********************************

Dim strStaff As String, dteBirthDate As String,intMatchpatient As Integer,
strCriteria As String
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Dim dbs As Database, rst As Recordset
Dim strname As String


Set dbs = CurrentDb()
' Create dynaset-type Recordset object.
Set rst = dbs.OpenRecordset("qryfrmNewPatient", dbOpenDynaset)
strname = [LastName] & ", " & [FirstName]
rst.FindFirst "Patientname = '" & strname & "'"

If rst.NoMatch Then
Exit Sub
Else
strStaff = DLookup("StaffAssigned",
"qryFindaMatch", "[PatientName] =" & _
"[Forms]![frmNewPatient]![PatientName]")
dteBirthDate = DLookup("DateOfBirth",
"qryFindaMatch", "[PatientName] ='" & strname & "'")
Msg = " The Patient: " & strname & vbCrLf &
vbCrLf & " Was Assigned to: " & _
" <" & strStaff & ">" & vbCrLf & vbCrLf & _
"Date Of Birth: " & dteBirthDate & vbCrLf &
vbCrLf & _
"This patient already exist! Please verify
this patient from the blue box"
Style = vbYes + vbInformation +
vbDefaultButton1
Title = "Find An Assigned Staff Name"
Response = MsgBox(Msg, Style, Title)

Me.Undo
Me!LookUp.Requery
Me!MatchStaff.Requery
Me.Requery
End If

rst.Close
Set dbs = Nothing
Me.Refresh

*******************************************end
can some one help with may be a better code?
what I need is to compare the names entered to the names in the database.
one problem is that I would like to account for hyphens and accents.
thanks
Al

 

[Stefan Hoffmann]

hi Al,

one problem is that I would like to account for hyphens and accents.

Use SoundEx or the Levenshtein distance
(http://en.wikipedia.org/wiki/Levenshtein).


mfG
--> stefan <--

 

[Douglas J Steele]

Even with Stefan's suggestion, I think you need to rethink your approach.
Last name and First name are seldom sufficient to uniquely identify an
individual. Look at George Foreman: all five of his sons are named George
Foreman...

 

[Guest]

I am using the data of birth as well. I just wanted to simplify the problem
here. do you have any suggestions?
thanks
Al

Even with Stefan's suggestion, I think you need to rethink your approach.
Last name and First name are seldom sufficient to uniquely identify an
individual. Look at George Foreman: all five of his sons are named George
Foreman...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a database in access97. In the database there is a form where the user
takes names of clients as they come. I want to check after the user enters
the first and last name if the name exist in the data base, if it does it
should alert the data entry person. I am using the following code which all
of the sudden stoped functioning the way it should, i.e. there are similar
name that the code missed:

start********************************

Dim strStaff As String, dteBirthDate As String,intMatchpatient As Integer,
strCriteria As String
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Dim dbs As Database, rst As Recordset
Dim strname As String


Set dbs = CurrentDb()
' Create dynaset-type Recordset object.
Set rst = dbs.OpenRecordset("qryfrmNewPatient", dbOpenDynaset)
strname = [LastName] & ", " & [FirstName]
rst.FindFirst "Patientname = '" & strname & "'"

If rst.NoMatch Then
Exit Sub
Else
strStaff = DLookup("StaffAssigned",
"qryFindaMatch", "[PatientName] =" & _
"[Forms]![frmNewPatient]![PatientName]")
dteBirthDate = DLookup("DateOfBirth",
"qryFindaMatch", "[PatientName] ='" & strname & "'")
Msg = " The Patient: " & strname & vbCrLf &
vbCrLf & " Was Assigned to: " & _
" <" & strStaff & ">" & vbCrLf & vbCrLf & _
"Date Of Birth: " & dteBirthDate & vbCrLf &
vbCrLf & _
"This patient already exist! Please verify
this patient from the blue box"
Style = vbYes + vbInformation +
vbDefaultButton1
Title = "Find An Assigned Staff Name"
Response = MsgBox(Msg, Style, Title)

Me.Undo
Me!LookUp.Requery
Me!MatchStaff.Requery
Me.Requery
End If

rst.Close
Set dbs = Nothing
Me.Refresh

*******************************************end
can some one help with may be a better code?
what I need is to compare the names entered to the names in the database.
one problem is that I would like to account for hyphens and accents.
thanks
Al

 

[Guest]

this looks interesting, How you apply it to access 97 though?
thanks
Al

 

[Stefan Hoffmann]

hi Al,

this looks interesting, How you apply it to access 97 though? http://www.creativyst.com/Doc/Articles/SoundEx1/SoundEx1.htm
http://www.merriampark.com/ld.htm#OTHER

mfG
--> stefan <--

 

[Douglas J Steele]

I wrote about both techniques in my April, 2005 "Access Answers" column in
Pinnacle Publication's "Smart Access".

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html (And Al, the sample
database is actually in Access 97 format!)

 

[Guest]

Thank you Douglas. I will try it and let you know how it came out with me.
Al

 

[Guest]

stefan,
thanks for your help. I will check this out and let you know what happened.
Al

 

[Guest]

thank you every one. actually Doug's database with the exampl works well even
with the accent. I would recommend it to every one having this problem.
thanks again.
Al