Query Issue

[Flora2021]

Hi, I have a query that I am trying to use criteria from two columns.
The one is "type" and the other is "status"

I have entered the type as a criteria such as "RO"
For the status column, I wanted this to be variable for the user to enter the status such as Open, closed, in process etc.
Basically it would return all records where the type is "RO" and the status would be whatever the user enters in the prompt".
Well the issue I am having is that if we actually enter the status of say Open, then the query is pulling all open records, but its pulling for all types, such as RO, Claims, Dmages, etc. Its no longer filtering on the type in entred in the criteria. However if I leave the status blank, it only pulls the "RO" criteria. I also just tried removing the "enter status or leave blank" prompt and entered the Open, In process, on hold status in the criteria and the OR fields and its doing the exact same thing. I am sure this is something similar i am doing wrong so I would appreciate any help. Thanks

 

[Joe4]

That is because are entering the criteria on TWO criteria lines instead of one.
Note that on the far left, under the word "Criteria" is the word "Or". This means if you use multiple criteria lines, it will treat it as an "OR" situation, and it sounds like you want an "AND" situation.
So you would need to put the Criteria for the different fields on the SAME Criteria row (the first one), to treat it like an "AND" situation.

 

[Flora2021]

That is because are entering the criteria on TWO criteria lines instead of one.
Note that on the far left, under the word "Criteria" is the word "Or". This means if you use multiple criteria lines, it will treat it as an "OR" situation, and it sounds like you want an "AND" situation.
So you would need to put the Criteria for the different fields on the SAME Criteria row (the first one), to treat it like an "AND" situation.

Thank you. I am confused. How would I enter this? Just to clarify, My criteria in first column says "RO" for Risk and Opportunity. Not OR. Thanks,

 

[Joe4]

Just Move the parameter under the "Status" field of "[Enter a status, leave blank to show all]" up one line so it is on the SAME criteria row as the "RO" Criteria under the "Type" field.
And totally delete the other field next to it that I show crossed out below:

Remember:
- Criteria listed on the SAME Criteria row are treated as AND
- Criteria listed on SEPARATE Criteria rows are teated as OR

 

[Flora2021]

Thank you So the first part is working now where it is pulling the correct records, when i enter a status, but when i leave blank it shows no records.

 

[Joe4]

I am not in front of a computer with Access right now, so I am doing this off of memory, but I think maybe you can change that parrmeter to something like this:

Like [Enter a status, leave blank to show all] & "*"

See if that works.

If not, I am guessing that you got your original structure from this link here: Return All Records using Query Parameters | Database Solutions for Microsoft Access | databasedev.co.uk
Try going back to that strucutre, and try adding the "RO" Criteria under the "Type" field under BOTH Criteria rows being used.

 

[Flora2021]

That worked Thank you so much!!

 

[Joe4]

You are welcome.
Out of curiosity, which method did you go with?

 

[Flora2021]

Like [Enter a status, leave blank to show all] & "*"

 

[Joe4]

Like [Enter a status, leave blank to show all] & "*"

Excellent!
I was doing that off of memory (hadn't done that in a few years). So it is good to know that the old gray matter still works (once in a while!).