OR code not working 2

[Dophia]

Hello Everyone;
I have a form which the user enters a person's name, then uses a button to call the following code. It works, but I also want to add an "OR" and nothing I do works.
Or "tblPeople WHERE Soundex((Nz[Spouse_CommonLaw_Last_Name])= '" & Soundex(Me.txtLastName) & " '"))

Original code without the above OR statement:

Private Sub CheckNames_Click()
Dim varID As Variant
Dim rst As DAO.Recordset, strNames As String
FROM tblPeople
If Not IsNothing(Me.txtLastName) Then
Set rst = DBEngine(0)(0).OpenRecordset("SELECT Last_Name, Spouse_CommonLaw_Last_Name, Spouse_Commonlaw_First_Name, Home_Phone_No FROM " & _
"tblPeople WHERE(Soundex([Last_Name]))= '" & Soundex(Me.txtLastName) & "'")

Any help would be appreciated.
Thanks, Sophia

 

[randy700]

How about a UNION query?

"SELECT Last_Name, Spouse_CommonLaw_Last_Name, Spouse_Commonlaw_First_Name, Home_Phone_No " & _
"FROM tblPeople " & _
"WHERE Soundex(Last_Name) = '" & Soundex(Me.txtLastName) & "'" & _
"UNION " & _
"SELECT Last_Name, Spouse_CommonLaw_Last_Name, Spouse_Commonlaw_First_Name, Home_Phone_No " & _
"FROM tblPeople " & _
"WHERE Soundex(Nz(Spouse_CommonLaw_Last_Name) = '" & Soundex(Me.txtLastName) & " '")

Randy

 

[Dophia]

Randy: I have never used a Union Query. Could you give me more details on how I would incorporate your code?

Thanks, Sophia

 

[CaptainD]

Hi Dophia

First off, I've never used a Soundex so I have been reading on it.

I looked closer at your code instead of the SQL string and I duplicated your Soundex() and table.

If I enter "Adams" for the name and run that through the Soundex function it returns "A352" which means the select statement spelled out would look like:

"SELECT Last_Name FROM tblPeople WHERE Last_Name = 'A352'"

and of course there is no last name like that.

Hence your original code

("SELECT Last_Name, Spouse_CommonLaw_Last_Name, Spouse_Commonlaw_First_Name, Home_Phone_No FROM " & _
"tblPeople WHERE[COLOR=blue](Soundex([Last_Name]))[/color]= '" & Soundex(Me.txtLastName) & "'")

was trying to run the "Field" through the soundex which it will not do. (At least it should not be able to run (Other then you say it works, when I pasted your code it throws an error)) Anyway

I would think that to make it work you would need to add a "Last_NameSDX" field and when you insert / update your table you would run the name to be saved through the Soundex and save that value so my select statement would look like.

"SELECT Last_Name FROM tblPeople WHERE Last_NameSDX = '" & SOUNDEX(LTRIM(txtlastName.Text)) & "'"

You might also look into using the "Like" operator in your select statement.

"SELECT Last_Name FROM tblPeople WHERE Last_Name LIKE '*" & LTRIM(txtLastName.Text)) & "*'"

 

[CaptainD]

Sorry, I looked at the "LIKE" statement and it might not work that way.

Should be

"SELECT Last_Name FROM tblPeople WHERE Last_Name LIKE '" & LTRIM(txtLastName.Text)) & "*'"

No preceeding "*" wildcard
I'm going to paly whit your code a little more to see what I can come up with.

 

[Dophia]

Thanks CaptainD!!

Did you use the following code? I made a copy of my form and pasted this code which does work. The Soundex code also has other code....Private Function GetSoundexCode, which is pasted below. Could that be the reason it didn't work when you tried it?

Original Code which works:
Private Sub CheckNames_Click()
Dim varID As Variant
Dim rst As DAO.Recordset, strNames As String
' Check for similar
If Not IsNothing(Me.txtLastName) Then
' Open a recordset to look for similar names
Set rst = DBEngine(0)(0).OpenRecordset("SELECT Last_Name, First_Name ,Home_Phone_No FROM " & _
"tblPeople WHERE Soundex([Last_Name]) = '" & _
Soundex(Me.txtLastName) & "'")
' If similar names, issue warning message
Do Until rst.EOF
strNames = strNames & rst!Last_Name & ", " & rst!First_Name & ", " & rst!Home_Phone_No & vbCrLf
rst.MoveNext
Loop
' Done with the recordset
rst.Close
Set rst = Nothing
' See if we there are some similar names
If Len(strNames) > 0 Then
' Yes, issue warning
If vbOK = MsgBox(gstrAppTitle & " found people with similar " & _
"last names already saved in the database: " & vbCrLf & vbCrLf & _
strNames & vbCrLf & "Click Cancel to go back to Find People Form, or Click OK to Close forms", _
vbQuestion + vbOK + vbDefaultButton2, gstrAppTitle) Then

SendKeys "{esc}", False

End If
End If
End If
End Sub




Private Function GetSoundexCode(strChar As String) As String
'-----------------------------------------------------------
' Input: One character
' Output: U.S. National archive "Soundex" number
' for the specified letter
' Created By: JLV 03/01/2003
' Last Revised: JLV 06/27/2005
'-----------------------------------------------------------
Select Case strChar
Case "B", "F", "P", "V"
GetSoundexCode = "1"
Case "C", "G", "J", "K", "Q", "S", "X", "Z"
GetSoundexCode = "2"
Case "D", "T"
GetSoundexCode = "3"
Case "L"
GetSoundexCode = "4"
Case "M", "N"
GetSoundexCode = "5"
Case "R"
GetSoundexCode = "6"
Case "H", "W"
' Special "skip" code
GetSoundexCode = "0"
End Select
End Function

 

[CaptainD]

That is the code I use other then I don't have your IsNothing() function so I made a change there.

The code as you posted it gives me an error that states "Run-Time error 3085: Undefined function 'Soundex' in expression"

When I change "WHERE Soundex([Last_Name])" to "WHERE [Last_Name]" it runs but it does not return anything.

Dim varID As Variant
Dim rst As DAO.Recordset, strNames As String
           ' Check for similar
[COLOR=green]'If Not IsNothing(Me.txtLastName) Then[/color]
txtLastName.SetFocus
If txtLastName.Text <> "" Then
[COLOR=green]' Open a recordset to look for similar names
'Set rst = DBEngine(0)(0).OpenRecordset("SELECT Last_Name, First_Name ,Home_Phone_No FROM " & _
'"tblPeople WHERE Soundex([Last_Name]) = '" & _
' Soundex(Me.txtLastName) & "'")[/color]
    Set rst = DBEngine(0)(0).OpenRecordset("SELECT Last_Name, First_Name ,Home_Phone_No FROM " & _
    "tblPeople WHERE [Last_Name] = '" & _
     Soundex(Me.txtLastName) & "'")
 
 ' If  similar names, issue warning message
Do Until rst.EOF
strNames = strNames & rst!Last_Name & ", " & rst!First_Name & ", " & rst!Home_Phone_No & vbCrLf
rst.MoveNext
Loop
' Done with the recordset
rst.Close
Set rst = Nothing
' See if we there are some similar names
 If Len(strNames) > 0 Then
 ' Yes, issue warning
 If vbOK = MsgBox(gstrAppTitle & " found people with similar " & _
       "last names already saved in the database: " & vbCrLf & vbCrLf & _
       strNames & vbCrLf & "Click Cancel to go back to Find People Form, or Click OK to Close forms", _
             vbQuestion + vbOK + vbDefaultButton2, gstrAppTitle) Then
                         
  SendKeys "{esc}", False

    End If
    End If
    End If

 

[Dophia]

CaptainD: Here is the IsNothing code. Maybe the changes you made for not having it has affected the result. I did try it again, and it does work and returns a pop up list of similiar names.
Sophia




Public Function IsNothing(ByVal varValueToTest) As Integer
'-----------------------------------------------------------
' Does a "nothing" test based on data type.
' Null = nothing
' Empty = nothing
' Number = 0 is nothing
' String = "" is nothing
' Date/Time is never nothing
' Inputs: A value to test for logical "nothing"
' Outputs: True = value passed is a logical "nothing", False = it ain't
' Created By: JLV 01/31/95
' Last Revised: JLV 01/31/95
'-----------------------------------------------------------
Dim intSuccess As Integer

On Error GoTo IsNothing_Err
IsNothing = True

Select Case VarType(varValueToTest)
Case 0 ' Empty
GoTo IsNothing_Exit
Case 1 ' Null
GoTo IsNothing_Exit
Case 2, 3, 4, 5, 6 ' Integer, Long, Single, Double, Currency
If varValueToTest <> 0 Then IsNothing = False
Case 7 ' Date / Time
IsNothing = False
Case 8 ' String
If (Len(varValueToTest) <> 0 And varValueToTest <> " ") Then IsNothing = False
End Select


IsNothing_Exit:
On Error GoTo 0
Exit Function

IsNothing_Err:
IsNothing = True
Resume IsNothing_Exit

End Function

 

[CaptainD]

Searching I found where someone is using a soundex as you originally posted it. I'm trying to duplicate it so I'll have to get back to you.

 

[CaptainD]

This works and pulls records from both fields.

There was something wrong with my first Access database so I rebuilt it and then added your code. It started working and so I made changes and this code runs.

Dim varID As Variant
Dim rst As DAO.Recordset, strNames As String
Dim strSearchName As String

Me.txtLastName.SetFocus
strSearchName = LTrim(Me.txtLastName.Text)
           ' Check for similar
If Not IsNothing(strSearchName) Then
' Open a recordset to look for similar names
Set rst = DBEngine(0)(0).OpenRecordset("SELECT Last_Name, First_Name ,Home_Phone_No FROM " & _
"tblPeople WHERE Soundex([Last_Name]) = '" & _
 Soundex(strSearchName) & "'" & _
 "OR Soundex(Spouse_CommonLaw_Last_Name) = '" & Soundex(strSearchName) & "'")
 ' If  similar names, issue warning message
Do Until rst.EOF
strNames = strNames & rst!Last_Name & ", " & rst!First_Name & ", " & rst!Home_Phone_No & vbCrLf
rst.MoveNext
Loop
' Done with the recordset
rst.Close
Set rst = Nothing
' See if we there are some similar names
 If Len(strNames) > 0 Then
 ' Yes, issue warning
 If vbOK = MsgBox(gstrAppTitle & " found people with similar " & _
       "last names already saved in the database: " & vbCrLf & vbCrLf & _
       strNames & vbCrLf & "Click Cancel to go back to Find People Form, or Click OK to Close forms", _
             vbQuestion + vbOK + vbDefaultButton2, gstrAppTitle) Then
                         
  SendKeys "{esc}", False

    End If
    End If
    End If

I'm going to play with it some more but cut and paste and let me know.

 

[CaptainD]

This one is a little different in that it only runs txtLastName through the Soundex function once.

Dim varID As Variant
Dim rst As DAO.Recordset, strNames As String
Dim strSearchName As String

Me.txtLastName.SetFocus
strSearchName = Soundex(LTrim(Me.txtLastName.Text))
           ' Check for similar
If Not IsNothing(strSearchName) Then
' Open a recordset to look for similar names
Set rst = DBEngine(0)(0).OpenRecordset("SELECT Last_Name, First_Name ,Home_Phone_No FROM " & _
"tblPeople WHERE Soundex([Last_Name]) = '" & _
 strSearchName & "'" & _
 "OR Soundex(Spouse_CommonLaw_Last_Name) = '" & strSearchName & "'")
 ' If  similar names, issue warning message
Do Until rst.EOF
strNames = strNames & rst!Last_Name & ", " & rst!First_Name & ", " & rst!Home_Phone_No & vbCrLf
rst.MoveNext
Loop
' Done with the recordset
rst.Close
Set rst = Nothing
' See if we there are some similar names
 If Len(strNames) > 0 Then
 ' Yes, issue warning
 If vbOK = MsgBox(gstrAppTitle & " found people with similar " & _
       "last names already saved in the database: " & vbCrLf & vbCrLf & _
       strNames & vbCrLf & "Click Cancel to go back to Find People Form, or Click OK to Close forms", _
             vbQuestion + vbOK + vbDefaultButton2, gstrAppTitle) Then
                         
  SendKeys "{esc}", False

    End If
    End If
    End If

 

[Dophia]

But the Soundex already works. When I use it on the last name, it works and then when I use it on the Spouse_CommonLaw_Last_Name it works. The problem is when I try to get the code to search either field with an OR.
That is where the problem happens.

Sophia

 

[Dophia]

CaptainD: I sent the other post before I received your last two posts.

IT WORKS!!!! Thank you!! I really appreciate the time and patience you had to work this out!!!!

THANK YOU, Sophia
PS
The only thing I had to add was the "Nz" since my Spouse_CommonLaw_Last_Name field is sometimes blank. I also added to the "Open.recordset" and to the "strNames = strNames & rst!" the "Spouse_CommonLaw_Last_Name, Spouse_CommonLaw_First_Name" so that it would display in the pop up.

 

[CaptainD]

Glad it works, we both learned from this one!

Thansk for the post...

 

[TheAceMan1]

CaptainD . . .

AceMan back from the hoildays. [blue]Good job![/blue] & happy New Year! . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[PHV]

If you don't like to use the OR operator:

Set rst = DBEngine(0)(0).OpenRecordset("SELECT Last_Name, First_Name, Home_Phone_No FROM " & _
 "tblPeople WHERE '" & Soundex(Me!txtLastName) & "' In (Soundex([Last_Name]," & _
 "Soundex(Spouse_CommonLaw_Last_Name))")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dophia]

PH: Thanks for the response.
I'll keep this in mind.
Sophia