Multiple, but variable amount, user defined criteria issue

[RobOtowski]

My apologies if this has been answered but I absolutely cannot find it.



I have inherited a "trouble ticket" database that we want to make searchable. Fields to search are subject, tags, and description. I have a form with 4 blank Text Boxes where users can input a search term, then search any of those 3 fields. The Text Boxes are labelled [Tag1], [Tag2], [Tag3], and [Tag4]. The basic set up is to search for anything that contains the terms from ALL of the text boxes. So the criteria within the query is
Like "*" & [Forms]![SearchForm]![tag1] & "*" And Like "*" & [Forms]![SearchForm]![tag2] & "*" And Like "*" & [Forms]![SearchForm]![tag3] & "*" And Like "*" & [Forms]![SearchForm]![tag4] & "*"
This works exactly how I want it to work. Users don't have to enter the EXACT word, just enough characters to get close (so "proj" finds "projector", "projecter", "project", etc). If a user enters only 2 terms (e.g. "proj" and "129") it returns only those tickets with BOTH "proj" and "129" in the appropriate field. If they use less than all 4 text boxes it still does exactly what I want, returning only the appropriate tickets. If they leave all 4 blank it returns all the tickets.

Now I have a user who wants to be able to search using EITHER [Tag1] OR [Tag2] OR... etc. So "proj" and "129 would find all tickets with either "proj" OR "129" (or both, that's fine here, too). So I thought I would use the above criteria but replace "and" with "or". The problem is that if any of the fields is blank it returns all the tickets.

So my question is how do I set up a query that will pull its criteria from the form, but will not try to use the [Tag]s that are left empty? So in regular, non-code speak it would be "If there's a value in [Tag1], search for that and then if there's a value in [Tag2], search for that, too. Go through all 4 unless they're empty, then just pretend the empty ones don't exist."

Everything that I've found that is semi-related to this seems to be the exact opposite of my situation. I don't know how it would be that everyone else is getting zero results from blanks and I get every result from blanks...

Any light you can shed on this would be so greatly appreciated!

If it's relevant I'm using Access 2013. Also, I should say that I have such limited experience with SQL language and VBA that if my solution lies in those areas you have to speak slowly for me to get it!

[Micron]

The answer likely depends on what the user wants, which in truth, you really don't know. Just because only 2 fields are filled doesn't mean that an either/or search is wanted, right? Considering only two pieces of criteria, it could mean a search where both conditions must be met as a pair. Or it could mean records that contain either criteria. I think you'll need a means to give the user a choice, such as with option buttons - one for each situation you may have.
What drives the opening of the query - a button click? If so, you can choose which query syntax is required based on the option frame value and run the appropriate one. Either open the correct query or in code, or build the WHERE clause separately from the main part of the query, based on the selected option.

[RobOtowski]

Micron, Thanks for the quick response!

The whole thing works (or will work [knock wood]) like this: Database opens to [forms]![searchform]. There's a Label with some "how to use this" information, then the four Text Boxes. It will have the "And/Or" option box once I figure out how to do "or". Then there are 4 Command buttons: "Search Tags", "Search Subject", "Search Comments", and "Clear Fields". "Clear fields" will clear the four Text Boxes. The others open a report which is based on a query where the appropriate field (either tags, or subject, or comment) has the Criteria I listed above (Like "*" & [forms]!.... you get the idea). Like I say this criteria works exactly how i want it to for "And", but I can't for the life of me figure out "or"...

I really don't know how to build a WHERE clause. I know to what you are referring, but I don't understand the SQL language enough to be able to just type them in, I have to at least start with the query builder in design view and then sometimes I can figure out how to manipulate it in SQL from there.

[ssanfu]

Its not the SQL you have to worry about, its the VBA. Building a flexible search form requires a lot of VBA.

Allen Browne has an article on searching at http://www.allenbrowne.com/ser-62.html

Then there is an example at "The Access Web" http://access.mvps.org/access/forms/frm0045.htm


What you are trying to do can be done, but it will take a fair amount of VBA.

[RobOtowski]

Thanks, ssanfu. I'm not afraid of VBA, as such, I'm just terribly unfamiliar with Access VBA. I've used Excel VBA a fair amount and all I've really learned about Access VBA is that it's not really the same thing! Thanks for pointing me in a good direction!

[Micron]

Two robust solutions for sure. From what I can tell, the first will not allow you to perform OR searches. If you don't need all that power, I suggest you build 2 or 3 WHERE clauses (assuming you don't need a whole lot of options). When they return what you need, assign them to svWhere variables and choose which to append to the main body of a sql statement that you build in code. The alternative is a separate query for each. Regardless, keep in mind that users who substitute numbers for text will likely get no results. Some validation of data types entered is important.

[orange]

I answered a similar post recently and included a sample database here.

[RobOtowski]

Wow. Okay, I'm beginning to understand what needs to be done. I see that it's far more complicated than I had originally thought (and hoped!), but not so complicated that I can't get it done. Orange, thanks so much for the example DB! It's not quite what I'm looking for, but it not only points me in the right direction but shoves me down the path! I genuinely appreciate your help.

I have a lot of VBA to learn for this, but I can already see where it will greatly benefit other databases that I work with. Thank you all so much!

[orange]

You are welcome. Happy to help.

More info and links that may be useful

-the set of 8 free videos by Dr Daniel Soper on Database starting here

- a full series of Access and programming videos (70+) by Steve Bishop

[Micron]

I like the orange version better for its simplicity. RobOtowski, if I understood your first post, you still won't have the ability to perform AND and OR approaches unless you provide separate sql constructs, which I see as being option button values passed to an option frame. Sorry if I've misunderstood your need. So you can have the benefit (?) of another method, here's how I have approached this in the past, using orange's code as a starting point:

Dim SQL As String, svCrit As String, svOrderBy As String 

SQL = "SELECT * from Voters WHERE "
svOrderBy = " ORDER By  VoterLastname" 
If Frame1 = 1 Then 'option for records must contain all criteria  supplied 
  If Me.txtFirstName = vbNullString Then Me.txtFirstName = "*"
  If Me.txtLastName = vbNullString Then Me.txtLastName = "*"
  If Me.cboDistrict = vbNullString Then Me.cboDistrict = "*"
  svCrit = "VoterFirstname Like '" & Me.txtFirstName & "*' AND VoterLastname  Like '"
  svCrit = svCrit & Me.txtLastName & "*' AND VoterDistrict Like '*" & Me.cboDistrict & "'"
Else 'option for records will contain any criteria supplied
 svCrit = "VoterFirstName = '" & Me.txtFirstName & "' OR VoterLastName = '"
 svCrit = svCrit & Me.txtLastName & "' OR voterAddrSt = '" & Me.cboDistrict & "'"
End If

SQL = SQL & svCrit & svOrderBy
' Debug.Print SQL
Me.VoterSubform.Form.RecordSource = SQL
Me.VoterSubform.Form.Requery
Me.txtRecs = DCount("ID", "Voters", svCrit)
End Sub

This is how I've constructed various sql statements based on user options where an AND, OR or even a UNION sql statement. Option button labels might explain that "Records returned will include ALL criteria options supplied" vs "Records returned will include ANY criteria options supplied" or something like that. My option button values are 1 and 2 respectively. The chosen value gets passed to the frame, which I simply named Frame1. Note the insertion points for spaces are different in the clauses. I have left out the wildcards for simplicity, so as written, it requires exact matches (i.e. Av will not return Ava). Also, I would set the option on form opening so that a user will not cause an error by not selecting an option:

Private Sub Form_Open(Cancel As Integer)
Me.Frame1 = 1
End Sub

[orange]

I agree with Micron that you should consider the database I provided as a starting point. Also, you could use a control to select AND/OR. The example db demonstrates searching based on revising some SQL statement behind the scene.
The default for the text box values is to look for terms Starting with whatever characters you enter.
You can also use a term such as *ng and that will find records where "ng" occurs anywhere in the field being searched.

eg: Kingston Jung Young...

There are several ways to "skin the cat", so investigate, do some trial and error....

Good luck.

[ssanfu]

The sites I posted was just as a reference - to see what the VBA might look like. It is easy for a person the handle/ understand using "And"/ "or" when searching, but difficult to program. "The Access Web" site has a dB that does include mixing "And"/ "or" searches.

I do not like to modify the SQL of a form/ control every time there is a new search. My SQL search forms do not have the "WHERE" clause; it is easier to just set the filter property when searching.
The full record set is displayed; then it is very obvious when a search does not return any records.

[RobOtowski]

Thanks, gang! I certainly have a lot to digest now. I wish there had been a simpler answer, but I really do enjoy getting to learn more about how all this works, so that I can make my databases do EXACTLY what I want them to do, rather than getting "close enough". I'll keep working at it.

[Micron]

it is easier to just set the filter property when searching.

How would you filter a set of OR records (where Foo = A OR Bar = B) and a set of AND records (where Foo = A AND Bar = B) or the result of a Union query, by applying a filter to the same form? The recordsets are exclusive of one another, are they not? It may be easier, but as far as I can tell, it would not provide what OP says was needed in the original post. Or have I misinterpreted what was written:

So "proj" and "129 would find all tickets with either "proj" OR "129"

I took that and the other scenario to mean a form with the capability to do both was needed.

[ssanfu]

You would develop the criteria string using VBA, then set the filter property to the criteria string and turn on the filter.

How would you filter a set of OR records (where Foo = A OR Bar = B) and a set of AND records (where Foo = A AND Bar = B) or the result of a Union query,

Parentheses would have to be used to do the groupings of the OR's and AND's. That is where the rub is.
People understand what they want, but how do you convert what you what to programming? How do you set up the form to allow "AND/OR" selections?

With jsut 3 fields, do you want;
1) (Field1 = "proj" or Field2 = "Curr") AND Field3 = 129

or do you want
2) Field1 = "proj" or (Field2 = "Curr" AND Field3 = 129)

Designing a query like this in query design view is easy... not so much in VBA.


I have limited criteria to "ANDs" in my search forms because I didn't have the time to try and develop the logic for mixing "AND and "OR" criteria.
And I have not taken the time to work through the code in the "FindRecord2K" dB - but I should.