Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

OR code not working 2

Status
Not open for further replies.
Dophia

Dophia

Technical User
Joined
Jul 26, 2004
Messages
263
Location
CA

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

 
Sort by date Sort by votes

try:

Code: 
("SELECT Last_Name, Spouse_CommonLaw_Last_Name, Spouse_Commonlaw_First_Name, Home_Phone_No FROM " & _
"tblPeople WHERE((Soundex([Last_Name]))= '" & Soundex(Me.txtLastName) & "'") OR Soundex(Nz([Spouse_CommonLaw_Last_Name]))= '" & Soundex(Me.txtLastName) & " '"))

Not sure about the braces but you only need to use "WHERE" one time
 
Upvote 0 Downvote

Thanks for your help. When I enter it as you suggested, I get an error " Compile error: Syntax error".

Any suggestions?
Sophia
 
Upvote 0 Downvote
How are ya Dophia . . .

. . . a first shot:
Code: 
[blue]   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   If Not Trim(Me.txtLastName & "") <> "" Then
      Set db = CurrentDb
      LN = Soundex(Me.txtLastName)
      LN = Soundex(Me.txtLastName)
      SQL = "SELECT Last_Name, " & _
                   "Spouse_CommonLaw_Last_Name, " & _
                   "Spouse_Commonlaw_First_Name, " & _
                   "Home_Phone_No " & _
            "FROM tblPeople " & _
            "WHERE (Soundex([Last_Name])= '" & Soundex(Me.txtLastName) & "') OR " & _
                  "(Soundex([Spouse_CommonLaw_Last_Name])= '" & Soundex(Me.txtLastName) & "')"
      Set rst = db.OpenRecordset(SQL, dbOpenDynaset)[/blue]
If this doesn't work, you need to explain what the function [blue]Soundex()[/blue] does . . .

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
 
Upvote 0 Downvote
Aceman: Thanks for your help. When I use the code you suggested, nothing happens. The Soundex function checks for similiar names, but without the additional "OR", the code works fine.

Here is my full code, which works as is, but I want to add another field to be searched....[Spouse_CommonLaw_Last_Name]. When I try to add it to the code, I get an error "Compile Error: Syntax Error"


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

REVISED CODE, WHICH DOES NOT WORK

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 , Spouse_CommonLaw_Last_Name, Home_Phone_No FROM " & _
"tblPeople WHERE Soundex([Last_Name]) = '" & _
Soundex(Me.txtLastName) & "'") _
OR Soundex(Nz([Spouse_CommonLaw_Last_Name]))= '" & Soundex(Me.txtLastName) & " '"))
' If similar names, issue warning message
Do Until rst.EOF
strNames = strNames & rst!Last_Name & ", " & rst!Spouse_CommonLaw_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

Any Suggestions? Sophia
 
Upvote 0 Downvote
Dophia said:
[blue]The Soundex function checks for similiar names . . .[/blue]
Click to expand...
I should've asked . . . what does Soundex return?

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
 
Upvote 0 Downvote
It returns a list of names that are similar. So if I was to enter "vie", then it would return "vien", "viens", etc....if the names already existed. I am using it to find mis-spelled names.

This is the function.




Function Soundex(ByVal WordString As String, _
Optional SoundexLen As Integer = 4) As String

Dim Counter As Integer
Dim CurrChar As String

If SoundexLen > 10 Then
SoundexLen = 10
ElseIf SoundexLen < 4 Then
SoundexLen = 4
End If
SoundexLen = SoundexLen - 1

WordString = UCase(WordString)

For Counter = 1 To Len(WordString)
If Asc(Mid(WordString, Counter, 1)) < 65 Or _
Asc(Mid(WordString, Counter, 1)) > 90 Then
Mid(WordString, Counter, 1) = " "
End If
Next Counter
WordString = Trim(WordString)

If Len(Trim(WordString)) = 0 Then
Soundex = ""
Else
Soundex = WordString

Soundex = Replace(Soundex, "A", "0")
Soundex = Replace(Soundex, "E", "0")
Soundex = Replace(Soundex, "I", "0")
Soundex = Replace(Soundex, "O", "0")
Soundex = Replace(Soundex, "U", "0")
Soundex = Replace(Soundex, "Y", "0")
Soundex = Replace(Soundex, "H", "0")
Soundex = Replace(Soundex, "W", "0")
Soundex = Replace(Soundex, "B", "1")
Soundex = Replace(Soundex, "P", "1")
Soundex = Replace(Soundex, "F", "1")
Soundex = Replace(Soundex, "V", "1")
Soundex = Replace(Soundex, "C", "2")
Soundex = Replace(Soundex, "S", "2")
Soundex = Replace(Soundex, "G", "2")
Soundex = Replace(Soundex, "J", "2")
Soundex = Replace(Soundex, "K", "2")
Soundex = Replace(Soundex, "Q", "2")
Soundex = Replace(Soundex, "X", "2")
Soundex = Replace(Soundex, "Z", "2")
Soundex = Replace(Soundex, "D", "3")
Soundex = Replace(Soundex, "T", "3")
Soundex = Replace(Soundex, "L", "4")
Soundex = Replace(Soundex, "M", "5")
Soundex = Replace(Soundex, "N", "5")
Soundex = Replace(Soundex, "R", "6")

CurrChar = Left(Soundex, 1)
For Counter = 2 To Len(Soundex)
If Mid(Soundex, Counter, 1) = CurrChar Then
Mid(Soundex, Counter, 1) = " "
Else
CurrChar = Mid(Soundex, Counter, 1)
End If
Next Counter
Soundex = Replace(Soundex, " ", "")

Soundex = Mid(Soundex, 2)
Soundex = Replace(Soundex, "0", "")

Soundex = Soundex & String(SoundexLen, "0")
Soundex = Left(WordString, 1) & Left(Soundex, SoundexLen)
End If
End Function
 
Upvote 0 Downvote
Reading this it looks like you are trying to run through the Soundex function as a table field.
Code: 
WHERE [COLOR=red]Soundex([Last_Name])[/color] = '" & _
 Soundex(Me.txtLastName) & "'") 

Should be 

[COLOR=blue] WHERE [Last_Name] = '" Soundex(Me.txtLastName) & "'")[/color]
I do not see how that runs at all.
Try this version.
Code: 
Set rst = DBEngine(0)(0).OpenRecordset("SELECT Last_Name, First_Name , Spouse_CommonLaw_Last_Name, Home_Phone_No FROM " & _
"tblPeople WHERE [Last_Name] = '" & _
 Soundex(Me.txtLastName) & "'") _
 OR [Spouse_CommonLaw_Last_Name]= '" & Soundex(Nz(Me.txtLastName)) & " '")
 
Upvote 0 Downvote
Thank you CaptainD for your suggestion, but I get a Compile Error: Syntax Error.

I found the Soundex code on the internet. "Soundex is a standard algorithm for finding names that sound alike." As I mentioned earlier, it works without the "OR". I originally used it to enable the user to enter a Last_Name and before the update of the field, the Soundex code is run. It then shows a pop up list of similar names already in the database. Otherwise, the user could enter the same person over and over into the database.

For this instance, I am using a form for the user to enter a name in an unbound field. Then on a cmd button click, it checks to see if that name is already in the database. The pop up shows the last name, first name, phone number. But since I have another table field named "Spouse_CommonLaw_Last_Name", I want that field to be checked for the name also. Since sometimes, the last names of the couple are different.

Any suggestions?
I appreciate all of your suggestions. Sophia

 
Upvote 0 Downvote
Dophia . . .

Couldn't believe the SQL wasn't working so I parsed thru the thread again. My SQL was based on that in your post origination:
Code: 
[blue]Set rst = DBEngine(0)(0).OpenRecordset("SELECT Last_Name, [purple][b]Spouse_CommonLaw_Last_Name[/b][/purple], [purple][b]Spouse_Commonlaw_First_Name[/b][/purple], Home_Phone_No . . .[/blue]
This is in error with the following you have after the recordset is set:
Code: 
[blue]Do Until rst.EOF
strNames = strNames & rst![purple][b]Last_Name[/b][/purple] & ", " & rst!Spouse_CommonLaw_Last_Name & ", " & rst![purple][b]First_Name[/b][/purple] & ", " & rst!Home_Phone_No & vbCrLf . . .[/blue]
FirstName is not a part of the SQL in the first code block I show!

Also, if at least one record is returned bt the recordset you know you have at least one match.

Correcting we have:
Code: 
[blue]   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   Dim strNames As String, sdxLastName As String, NL As String, DL As String
   Dim Msg As String, Style As Integer, Title As String
   
   If Trim(Me.txtLastName & "") <> "" Then
      Set db = CurrentDb
      sdxLastName = Soundex(Me.txtLastName)
      NL = vbNewLine
      DL = NL & NL
      
      SQL = "SELECT Last_Name, " & _
                   "First_Name, " & _
                   "Spouse_CommonLaw_Last_Name, " & _
                   "Home_Phone_No " & _
            "FROM tblPeople " & _
            "WHERE (Soundex([Last_Name]) = '" & sdxLastName & "') " & _
                   "OR " & _
                   "(Soundex(Nz([Spouse_CommonLaw_Last_Name],'0'))= '" & sdxLastName & "')'"
      Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
      
      Do Until rst.EOF
         strNames = strNames & rst!Last_Name & ", " & _
                               rst!Spouse_CommonLaw_Last_Name & ", " & _
                               rst!First_Name & ", " & _
                               rst!Home_Phone_No & NL
         rst.MoveNext
      Loop
      
      If Len(strNames) > 0 Then
         Msg = gstrAppTitle & " found people with similar " & _
              "last names already saved in the database: " & DL & _
               strNames & NL & _
              "Click Cancel to go back to Find People Form." & DL & _
              "Click OK to Close forms"
         Style = vbQuestion + vbOKCancel + vbDefaultButton2
         Title = gstrAppTitle
         
         If MsgBox(Msg, Style, Title) = vbOK Then
            [green]'Your code to close forms here[/green]
         Else
            Me.Undo [green]'Cancel pressed[/green]
      End If
      
      Set rst = Nothing
      Set db = Nothing
   End If

End Function[/blue]

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
 
Upvote 0 Downvote
Thank you Aceman! I entered the code and the first error was something like...block if without end if, so I added an End If. Now I have a runtime error '3075': Syntax error in string in query expression '(Soundex([Last_Name]) = 'M620')OR(Soundex(Nz([Spouse_CommonLaw_Last_Name],'0'))= 'M620')".

Sophia
 
Upvote 0 Downvote
Dophia . . .

So sorry . . . my mistake. Code should be:
Code: 
[blue]  Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   Dim strNames As String, sdxLastName As String, NL As String, DL As String
   Dim Msg As String, Style As Integer, Title As String
   
   If Trim(Me.txtLastName & "") <> "" Then
      Set db = CurrentDb
      sdxLastName = Soundex(Me.txtLastName)
      NL = vbNewLine
      DL = NL & NL
      
      SQL = "SELECT Last_Name, " & _
                   "First_Name, " & _
                   "Spouse_CommonLaw_Last_Name, " & _
                   "Home_Phone_No " & _
            "FROM tblPeople " & _
            "WHERE (Soundex([Last_Name]) = '" & sdxLastName & "') " & _
                   "OR " & _
                   "(Soundex(Nz([Spouse_CommonLaw_Last_Name],'0'))= '" & sdxLastName & "')'"
      Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
      
      Do Until rst.EOF
         strNames = strNames & rst!Last_Name & ", " & _
                               rst!Spouse_CommonLaw_Last_Name & ", " & _
                               rst!First_Name & ", " & _
                               rst!Home_Phone_No & NL
         rst.MoveNext
      Loop
      
      If Len(strNames) > 0 Then
         Msg = gstrAppTitle & " found people with similar " & _
              "last names already saved in the database: " & DL & _
               strNames & NL & _
              "Click Cancel to go back to Find People Form." & DL & _
              "Click OK to Close forms"
         Style = vbQuestion + vbOKCancel + vbDefaultButton2
         Title = gstrAppTitle
         
         If MsgBox(Msg, Style, Title) = vbOK Then
            'Your code to close forms here
         Else
            Me.Undo 'Cancel pressed
         [purple][b]End If[/b][/purple]
      End If
      
      Set rst = Nothing
      Set db = Nothing
   End If[/blue]

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
 
Upvote 0 Downvote
Geeze . . . Hit submitt before posting! [blush] Try:

Code: 
[blue]  Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   Dim strNames As String, sdxLastName As String, NL As String, DL As String
   Dim Msg As String, Style As Integer, Title As String
   
   If Trim(Me.txtLastName & "") <> "" Then
      Set db = CurrentDb
      sdxLastName = Soundex(Me.txtLastName)
      NL = vbNewLine
      DL = NL & NL
      
      SQL = "SELECT Last_Name, " & _
                   "First_Name, " & _
                   "Spouse_CommonLaw_Last_Name, " & _
                   "Home_Phone_No " & _
            "FROM tblPeople " & _
            "WHERE (Soundex([Last_Name]) = '" & sdxLastName & "') " & _
                   "OR " & _
                   "(Soundex(Nz([Spouse_CommonLaw_Last_Name],'0'))= '" & sdxLastName & "')'"
      Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
      
      Do Until rst.EOF
         strNames = strNames & rst!Last_Name & ", " & _
                               rst!Spouse_CommonLaw_Last_Name & ", " & _
                               rst!First_Name & ", " & _
                               rst!Home_Phone_No & NL
         rst.MoveNext
      Loop
      
      If Len(strNames) > 0 Then
         Msg = gstrAppTitle & " found people with similar " & _
              "last names already saved in the database: " & DL & _
               strNames & NL & _
              "Click Cancel to go back to Find People Form." & DL & _
              "Click OK to Close forms"
         Style = vbQuestion + vbOKCancel + vbDefaultButton2
         Title = gstrAppTitle
         
         If MsgBox(Msg, Style, Title) = vbOK Then
            'Your code to close forms here
         Else
            Me.Undo 'Cancel pressed
         End If
      End If
      
      Set rst = Nothing
      Set db = Nothing
   End If[/blue]

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
 
Upvote 0 Downvote
Aceman: I still get the same error.

Sophia
 
Upvote 0 Downvote
Dophia . . .

I'm on my way out to catch a train for New Years with my family. If there On Line . . . I'll pick this up tomorrow. If not, . . . I'll get back to you on the 3rd!

If it works out negative, have a very good newyear on me!


Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
 
Upvote 0 Downvote
Thank you for your help Aceman!! Happy New Year.
Sophia
 
Upvote 0 Downvote
Dophia . . .

Roger That! . . .

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
 
Upvote 0 Downvote
I see I did not complete the Nz() parameters. Should have been

Code: 
Set rst = DBEngine(0)(0).OpenRecordset("SELECT Last_Name, First_Name , Spouse_CommonLaw_Last_Name, Home_Phone_No FROM " & _
"tblPeople WHERE [Last_Name] = '" & _
 Soundex(Me.txtLastName) & "'") _
 OR [Spouse_CommonLaw_Last_Name]= '" & Soundex(Nz(Me.txtLastName[COLOR=blue], '0'[/color])) & " '")

Do you step through your code as it runs?
 
Upvote 0 Downvote
CaptainD:
I had to add Soundex in front of [Last_Name], and then the first part works...before the OR. There is a problem with the '" after [Spouse_CommonLaw_Last_Name].

I am at a loss as to why this doesn't work either.
Sophia
 
Upvote 0 Downvote
If I switch the Last_Name with Spouse_CommonLaw_Last_Name, the code works....but when I add the OR, it doesn't. The error is a "type mismatch" in the OpenRecordset.
Any suggestions? Sophia



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

Adding this gives me the error message.
'Or Soundex(Nz([Last_Name])) = " '" & Soundex(Nz(Me.txtLastName) & " '")
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Solved
Split Function not working
Replies
3
Views
800
Kennelbloke
Kennelbloke
Moss100
  • Locked
  • Question Question
Code not working
Replies
5
Views
591
Darrylles
Darrylles
Knicks
  • Question Question
Nested Column (Excel, CSV) : How to extract just the metadata or column names within a column of data
Replies
1
Views
73
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question Question
Access send email attachments - problem when null or empty
Replies
7
Views
4K
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question Question
Vba filter not working
Replies
7
Views
742
Cole Mendoza
Cole Mendoza

Part and Inventory Search

Sponsor

Back
Top