Search Multiple Fields

[JCorreia]

I'm trying to implement a macro to filter through accidents to see if my company has already been hired on a particular case.
In the first form the user enters the date, location, and last name of one of the drivers. Then they click a button which opens a new form as a datasheet with accidents that match ANY of the three controls. Evidently, I need a filter that contains some form of a series of OR operators.
However, whenever I try any variation of a button that opens the second form and then applies the filter, I always get no matches.
Does anybody have a suggestion for an alternative to this method, or a mistake that I'm making currently?

 

[JCorreia]

Also, I am using Access 2007.

 

[John Big Booty]

Perhaps the sample here is doing the sort of thing you are after.

 

[JCorreia]

Thanks! I managed to work something out using that code you gave for the query.

 

[JCorreia]

I seem to still be a bit stuck.
What I have currently are three text boxes, each of which the user may or may not input text into. Upon clicking a button, I run a query on a separate form which filters based on criteria such as
Like "*" & [Forms]![frmConflictSearch]![Date of Accident] & "*"
for Date, Location, and Last Name.
Each of the criteria are in separate OR rows in the query, and filter different fields in the separate form.
I'm trying to get it so that I end up with records where the Date matches the Date in frmConflictSearch, the Location matches the Location in frmConflictSearch, OR the Name matches the Name in frmConflictSearch. I also want the user to have the option of leaving any of the fields blank and simply not filtering based on that field.
However, whenever I try to leave one of the fields blank, the query returns every record in the database.
Can somebody give me some help on how to resolve this?

 

[John Big Booty]

The query is returning all records when you leave one of the field blank, as you are using the OR criteria, try using the AND criteria.

 

[JCorreia]

But if I want it to return records that match any of the three fields, wouldn't that require me to use OR? (Ie. WHERE field1.form = field1.record OR field2.form = field2.record OR field3.form = field3.record)

 

[JCorreia]

Actually, I think I've identified the problem.
I think it's my use of wildcards.
Whenever I use wildcards (ie. Like "*" & [field] & "*"), it returns any occurrence of the pattern I'm looking for, which is what I want, but if I leave that field blank, it returns every record, which is what I don't want.
Whenever I don't use wildcards, it allows me to leave that field blank, but only returns exact matches.
Is there any way I can get it to both return any occurrence of the pattern and have the possibility of leaving the field blank?
Thanks a bunch!

 

[Brianwarnock]

Try something like

Where (field1 like "*" & criterial & "*" or criterial is null)
Or ( ditto for the others

Brian

Edit I don't think this is correct, I think you need to switch your ORs to ANDs, although a blank criteria will allow all rows to be selected the selection will be filtered on the other criteria, which is what you want, which of course is what John said.

 

[rzw0wr]

I think you may need to change

Like "*" & [Forms]![frmConflictSearch]![Date of Accident] & "*"

to this

Like "*" & "#" & [Forms]![frmConflictSearch]![Date of Accident] & "#" & "*"

As Date of Accident is a date type.

Dale

 

[Brianwarnock]

You need # # around date strings not controls contains date values which will be in the Double stored data type.

Brian

 

[JCorreia]

Since I have an input mask on my dates, it turns out I didn't need the #'s.

But I think I finally solved it, for each of the criteria I put

(Like "*" & [Forms]![frmConflictSearch]![Date of Accident] & "*") AND ([Forms]![frmConflictSearch]![Date of Accident] Is Not Null)

Thanks a lot for everybody's help.

 

[Brianwarnock]

It is not the input mask that means that you do not need ## but that the dates are in controls and are there for represented by double data type the # are around #01/01/2013#

What you have coded by the And .. Is Not Null achieves the same as switching the Or between field criteria for AND in your initial attempt with one major difference now if all criteria are Null nothing is selected, whereas using
Field1 like "*" & criteria1 & "*" And Field2 like "*" & criteria2 & "*" etc everythink is selected.

It's case of which option you want.

Brian