Error on a search box (1 Viewer)

mtagliaferri · Jul 16, 2017

I have the following code that performs a search perfectly, but it will only search one parameter, I would like to be able to type in the search box two or more parameters to filter further down the records, example "Surname" & "EventType".
I want to keep only one search box in the form, any suggestions?

Code:

Private Sub CmdSearch_Click()
    Dim strSearch As String
    Dim strTxt As String
    strTxt = Me.TxtSearchBox.Value
    strSearch = "SELECT * from qryUpdatesTimeline where ((FirstName like ""*" & strTxt & "*"") or (Surname like ""*" & strTxt & "*"") or (EventType like ""*" & strTxt & "*""))"
    Me.RecordSource = strSearch
    Me.RecordSource = Replace(strSearch, "{0}", Me.TxtSearchBox)
    Me.TxtSearchBox = ""
End Sub

jdraw · Jul 16, 2017

?? You want to put 2 search criteria in the same text box???

CJ_London · Jul 16, 2017

perhaps provide an example of what you want your criteria to look like

I suspect what you are asking is not possible without the user effectively typing in the whole search string

mtagliaferri · Jul 16, 2017

The search box works perfectly if I type one word but if I try two words as example name and event type it will not search.
I would like first to filter all the names and the filter further by specifying an event type that, I believe I will need a second search box ....

jdraw · Jul 16, 2017

Sounds reasonable. Did you try a second textbox?

mtagliaferri · Jul 17, 2017

No I haven't, I believe that is the only option!

CJ_London · Jul 17, 2017

only thing I can suggest for a single searchbox is you could instruct the user to enter values separated by a +.

you would then split this into an array and apply the first element to firstname and lastname and the second element to eventtype. Don't forget each element uses an OR (per your existing code) but would be separated by an AND

You can extend this concept further so users entered

smith/jones+exhibition

having split on the +, you now split on the / in the first element so you are effective building the criteria

(firstname like smith or lastname like smith or firstname like jones or lastname like jones) AND eventtype like exhibition

Clearly this can be very limiting - you now no longer have a OR between the names and eventtype and restricted for general searches across all columns (although I guess you could code it so if there isn't a + use your present method)

Orthodox Dave · Jul 17, 2017

Since there is unlikely to be confusion between surname and event type, you wouldn't need to specify which was which. Just get them to input one parameter, or two parameters separated by commas.

Then you can parse the resulting string into two variables (or one variable and an empty string).

Your code can be much the same but repeated with an "AND" statement between (the empty string will still be surrounded by "*" so won't influence the result). Like this:

Code:

strSearch = "SELECT * from qryUpdatesTimeline where (((FirstName like ""*" & strTxt1 & "*"") " & _
"or (Surname like ""*" & strTxt1 & "*"") or (EventType like ""*" & strTxt1 & "*"")) " & _
"AND ((FirstName like ""*" & strTxt2 & "*"") or (Surname like ""*" & strTxt2 & "*"") " & _
"or (EventType like ""*" & strTxt2 & "*"")))"

jdraw · Jul 17, 2017

I agree with the solutions proposed - if you want to enter multiple parameters into a single textbox, then you will have to have logic to separate those parameters when you use each as criteria for your search.

I have posted a universal search that will look for one or more fragments in any field in any table. It may not be relevant to your specific issue, but does show how to process "multiple parameters" against multiple fields..

Good luck.

Error on a search box (1 Viewer)

mtagliaferri

Registered User.

jdraw

Super Moderator

CJ_London

Super Moderator

mtagliaferri

Registered User.

jdraw

Super Moderator

mtagliaferri

Registered User.

CJ_London

Super Moderator

Orthodox Dave

Home Developer

jdraw

Super Moderator

Similar threads

Users who are viewing this thread