Using criteria for a narrowed search

[Zebedee]

Hello all,
I am self taught and have created a database from scratch for portable appliance testing (PAT).

I would like to generate a query but so far mine doesn’t give me the results I expected.

I would like the user to input into the three query text boxes.
An input can be made into any or all of the three text boxes; i.e. room number, description, serial number.

My results using MS Access criteria doesn’t seem to give me what I need. It over populates.
How can I get the narrowed results please?

I am not up to speed with VBA programming etc., but am quite pc savvy otherwise.

Any help gratefully received.
Zebedee


Attachment 33666

[isladogs]

Welcome to the forum

Your attachment is invalid so I couldn't look at it

My results using MS Access criteria doesn’t seem to give me what I need. It over populates.
How can I get the narrowed results please?

Try selecting unique values in the query properties

[Zebedee]

Doc1.pdf

Many thanks for your reply. Not sure why the attachment can't be seen though.
The criteria I have used is as below, with the room no in the AND row and the others in the OR rows;

[Forms]![Mysearch]![Room No]
[Forms]![Mysearch]![Description]
[Forms]![Mysearch]![Serial Number]


Is this what you are referring to in your reply or am I still missing something?

Zeb

[isladogs]

The new screenshot is OK but it would help if you cropped your images before uploading so you only show the relevant parts

The query will give results if ANY of the conditions are true as you have used OR
If you only want results where ALL conditions are true you need AND instead - put all conditions on the same row to do this

[Zebedee]

Thank you for your comments.

I want to be able to enter any combination of the 3 fields i.e. 1 and 2, 1 and 3, 2 and 3. Or singularly, in other words only entering either field 1, field 2 or field 3.
I am getting nil results if I miss out a field.
The point of the search is to be able to enter however many fields that are known to the user.

Zeb

[June7]

If values are text, use AND with LIKE operator and * wildcard. Or VBA to construct filter criteria. Review http://allenbrowne.com/ser-62.html

[Zebedee]

Thank you for your Review download. This looks just the sort of thing I am looking for. I just now need to sit and digest what is going on within the db and learn how this sort of thing can be incorporated into my db.

As I mentioned earlier, I am self taught so this will keep me interested for some time.

Once I get my db to the finished state I will share my congratulations with all of you that have helped me.