Need textbox to search 2 fields 1

[LakotaMan]

Hi everyone, I'm having the devil of a time remembering the proper code to create a search textbox on my form that can be used to make "inexact" matches.

Here's the setup:

I have a form based on a table called Books with a couple of fields I want to search on: Title and AuthorLN.

Want to be able to type "power" and come up with the following books:
Power Broker
The Power of the Kabbalah

or type "frank" and come up with:
The Diary of a Young Girl (by Anne Frank)
Anne Frank in Holland

(all articles are in a separate field)

I need to be able to make approximate matches on either field --Title, AuthorLN.

I have researched other threads on this in the forum and can't find exactly what I want.

I started with just the Title field to play around with and have been trying to adapt this code I found on an old thread:

Private Sub FindABook_AfterUpdate()

    With Me.RecordsetClone
       Me.RecordsetClone.FindFirst "Title Like " & Chr(34) & "*" & Me.FindABook & Chr(34)
       If .NoMatch Then
          MsgBox "You must have dreamed that book up, cuz it ain't here!", vbExclamation, "REALLY?"
       Else
          Me.Bookmark = .Bookmark
       End If
    End With

End Sub

Doesn't quite do what I want and seems to work only after one try (matching up to beginning characters)

Am using Access 2010.

And help you can give me will be GREATLY appreciated!


Thanks,
Lakotaman

 

[kjv1611]

As a start, you could use a query instead.

So you're query could be something like

SELECT BookID, Title, Author, Date, WhateverElse
FROM Books
WHERE Title Like "*" & Forms!MyFormName!FindABook & "*"
UNION
SELECT BookID, Title, Author, Date, WhateverElse
FROM Books
WHERE Author Like "*" & Forms!MyFormName!FindABook & "*"

That's a start anyway.

And of course, to help cover for them entering spaces in a title... where they may get a word or two out of order, you could use an array to split up the entered text, and search for all variants... something like this:

Sub MyBookSearcher()
  Dim strBook() As String [green]'Array variable for capturing user input[/green]
  Dim strSQL() As String [green]'Query String - in array format[/green]
  Dim strStaticSQL As String [green]'to store final query string[/green]
  Dim x As Integer [green]'For looping through array[/green]
  
  If Instr(FindABook," ") Then
     strBook = Split(FindABook," ")
     Redim strSQL(UBound(strBook)*2) [green]'You'll need 2 SQL for Every 1 possible if doing the UNION way here..[/green]
     For x = LBound(strBook) to UBound(strBook)
        strSQL(x) = "SELECT BookID, Title, Author, Date, WhateverElse " & vbCrLf & _
                    "FROM Books " & _
                    "WHERE Title Like " & Chr(34) & "*" & strBook(x) & "*" & Chr(34)
        strSQL(x+1) = "SELECT BookID, Title, Author, Date, WhateverElse " & vbCrLf & _
                    "FROM Books " & _
                    "WHERE Author Like " & Chr(34) & "*" & strBook(x) & "*" & Chr(34)
        x = x + 1
     Next x
  Else
     Redim strSQL(1)
     strSQL(0) = "SELECT BookID, Title, Author, Date, WhateverElse " & vbCrLf & _
                 "FROM Books " & vbCrLf & _
                 "WHERE Title Like " & Chr(34) & "*" & FindABook & "*" & Chr(34)
     strSQL(1) = "SELECT BookID, Title, Author, Date, WhateverElse " & vbCrLf & _
                 "FROM Books " & vbCrLf & _
                 "WHERE Author Like " & Chr(34) & "*" & FindABook & "*" & Chr(34)
  End If

  For x = 0 to UBound(strSQL)
     If x = 0 Then
        strStaticSQL = strSQL(0)
     Else
        strStaticSQL = strStaticSQL & vbCrLf & "UNION" & vbCrLf & strSQL(x)
     End If
  Next x

  [green]'Then set your rowsource:... I'll just call it subResults[/green]
   Forms!frmMyMainForm!subResults.RowSource = strStaticSQL
   Forms!frmMyMainForm!subResults.Requery
End Sub

I cannot say for sure that the above will work - it's untested, I just typed as I thought it out. You will need to test it, and you'll need to change any field/object names as appropriate.

Post back with your progress.




"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57