Problem searching query

[UT227]

I’m using Access 2013. I’m trying to search a query in 6 different fields. I’m using the “Like” feature in each field. If I only use 1 search field, it works fine. But, trying to search more than that brings up a problem. Access looks at the field and if it’s null, it disregards that field. So, searching for something in one field, the other fields also have to be populated or Access disregards it because it’s null. I need Access to look at all fields regardless of it being null. I was told that I need to use the Nz feature. I’m not sure how it works or if it will work. Any ideas?

[cyanidem]

Can you show us your query at the present state?

[UT227]

I'm not sure how to show the query. I'm new to forums. Here's the command in the criteria box: Like "*" & [Forms]![frmWeaponsSearch]![LastName] & "*"
I'm using a form to do a search. The form is called [frmWeaponsSearch]. In that form, I fill out field and the query searches for what's in that field. If I only have one search, the process works like a charm. The problem is that I have multiple searches.

[orange]

In the query wizard where you built the query, switch to SQL view; copy the code you see and post it.

[cyanidem]

Ok, I guess your query design looks similar to this:



while it should look like this:



But it's a pure guess as you didn't provide any useful info

[UT227]

Yes. It's like that except that I've used different names in the fields:

[UT227]

[UT227]

I put them all in the same line of the criteria. I'm guessing I messed up on that.

[orange]

When you put the criteria on the same line it is an AND of the criteria.
On separate lines it is an OR of criteria.
As Cyanidem said,you want the OR

good luck.

[UT227]

I put them on different lines. Now, it's showing everything regardless of what I try to search for.

[cyanidem]

That's why we need to see your query, without it we can only guess wasting our and your time.

[rpeare]

What are you trying to accomplish

1. Find all records where the entered critera match?
2. Find all records where any of the entered criteria match?

For instance if you put
Mic
Mou

in your first name and last name and you have two people in your database

Minnie Mouse
Mickey Mouse

If you use the AND (all the criteria are on the same line)
and put in a criteria of

MIC in your first name
MOU in your last name both

Only Mic(key) Mou(se) will be selected

If you use the OR (all the criteria are on different lines)

Mic(key) Mou(se)
AND
Minnie Mou(se)

will be selected.

If you are going to force a match on a null field you may have to do it with a formula similar to

iif([Lastname] = forms!frmWeaponSearch!LastName or isnull(forms!frmWeaponSearch!LastName), 1, 0)

Then use a criteria of 1 on this field

Also note, if you are using LIKE on a date field or number field your query will fail.

[orange]

What exactly are you searching for? In which field/column would you find it?

[UT227]

[UT227]

I'm searching by name, weapon type, serial number, ID number, and location. I want to be able to enter any one of the fields and get only the results for that search. I keep trying to post a jpg, but it hasn't shown.