Search using and/or (1 Viewer)

[cricketbird]

I currently have a button that launches a msgbox that asks "Which item?" and the user types "Fred", and it opens a report filtered to all the records where a field is
like *Fred*..

This works perfectly. However, we now want to have users be able to type in "Fred or Ginger" (or Fred and Ginger) and it should find all the records that include either like *Fred* OR like *Ginger* (or like *Fred* AND like *Ginger*).

What's the best way to go about this? I was thinking about creating a separate form with multiple text boxes, but that gets a little hairy. Does anyone have an example of allowing users to enter somewhat complex queries in a query/report record search?

Thank you!

Existing code

Private Sub Command40_Click()
    Dim mystr As String
    mystr = InputBox("Which item?")
    Dim whr As String
    whr = "([ITEM] Like '*" & mystr & "*') AND [ACTIVE] = True"
    DoCmd.OpenReport "RecordReport", acViewPreview, , whr, , mystr
End Sub

 

[Josef P.]

The existing BuildCriteria function may already be sufficient.

Const FieldName As String = "ITEM"

Dim CriteriaString As String
Dim Expression As String

Expression = "*Fred* and *Ginger*"
CriteriaString = BuildCriteria(FieldName, dbText, Expression)
Debug.Print CriteriaString

Expression = "*Fred* or *Ginger*"
CriteriaString = BuildCriteria(FieldName, dbText, Expression)
Debug.Print CriteriaString

Expression = "Fred or Ginger"
CriteriaString = BuildCriteria(FieldName, dbText, Expression)
Debug.Print "!!! "; CriteriaString

Expression = "Fred or Ginger"
Expression = "*" & Replace(Expression, " or ", "* or *") & "*"
CriteriaString = BuildCriteria(FieldName, dbText, Expression)
Debug.Print CriteriaString

 

[plog]

but that gets a little hairy

What you want to do is inherently the hairy part, not implementing it in Access. Complexity has to exist somewhere--either in the code you write or in what is expected of the users.

Here's your options:

1. Just one text input. You allow users to type in their own criteria and you implement it. E.G "Fred Or Ginger", "Fred And Not Ginger", etc. Then you've got to parse whatever they input and also hope they do it in a consistent manner--you must explain the rules to them of what they must input.

2. Unlockable rows of inputs. You have 5 rows of inputs-all of which are hidden except a checkbox on the far left of the row. Check that box and it unhides the row of inputs and allows the user to add/edit a criterion. The second input in the row (after the checkbox) is a drop down that allows the user to select "And" or "OR". The third input is a text input where they can input a string. Then in the code you check which inputs are selected and parse it into a filter string as directed by what has been selected and input.

 

[arnelgp]

see also this demo. open form1 and see the code on the "Report" button.

 

[cricketbird]

Thank you everyone - I've learned a lot. The code examples and example db were extremely helpful! I will be using both of those in several locations.