Solved One criteria not filtering in Query

[M Costumes]

I've written a search query to search tbl: Inventory with cbo boxes to select the criteria on a form. I have one criteria that is not filtering and I can't quite figure out why--it is set up exactly like all the others.

SELECT Inventory.Photo1, Inventory.InventoryID, Inventory.SetID, Inventory.ItemName, Inventory.Description, Inventory.Period, Inventory.Color, Inventory.GarmentType, Inventory.GarmentSubType, Inventory.ATCShow, Inventory.BodyType, Inventory.Size, Inventory.ChestBust, Inventory.Waist, Inventory.Hip, Inventory.InOutSeam, Inventory.Condition, Inventory.RentalRate
FROM Inventory
where Like "*" & [Forms]![frmSearchInventory]![cboBodyType] & "*" Or [Forms]![frmSearchInventory]![cboBodyType] Is Null

No matter what is selected on the form for "BodyType" it will return all records that meet the other criteria, completely ignoring what was selected for "BodyType". Originally I had options for "BodyType" as child, female, male, neutral and I thought the issue might be the m-a-l-e in two words and the "*" picking up on that, so I changed them to M and F, but it didn't seem to help. All the records do have either a M or F value. Any thoughts? Happy to provide any additional info. Thanks!

 

[theDBguy]

Hi. But you only posted one of the filters/criteria. Not sure we can tell from that why it's not working. By itself, it should be working, right?

My guess is you need to add some brackets () to specify priorities or precedence with the filters.

My recommendation is to use VBA instead for creating the criteria for your query.

 

[M Costumes]

My full SQL is too long to post. I'm very new to all of this, so for the most part I've been letting Access do a lot of the heavy lifting, so the SQL is rather long and messy. Everything else is working really well, I added each criteria one by one to make sure, and this one is my problem child--ha! I've attached a copy of my file, there is only this one query.

@theDBguy do you have a recommendation for a crash-course in using VBA?

 

[theDBguy]

@theDBguy do you have a recommendation for a crash-course in using VBA?

Maybe you could start here.

Learn VBA By Crystal

 

[Isaac]

I agree with dbGuy...it's quite possible your problem lies in parentheticals and order of logic. There is nothing wrong with the Access QBE helper, it's one of Access's best features. I think Crystal has a special aspect of services that combines doing your development + teaching you -- kind of a neat deal.

Can you post your database? This could be a hard one to follow just in conversation & code.

 

[M Costumes]

Ha! I missed that it was too large when I tried to upload it with my previous reply. There's a zip attached to this one.

 

[Isaac]

WOWZER that's a lot of criteria in the qbe. I'll check it out and post back as I notice anything helpful

 

[Isaac]

Generally speaking, if you want a criteria to "always apply to all scenarios", it needs to be repeated on each line of the criteria grid.
Let's just take the first 2 lines (to simplify!) ....

You're saying: show me records where: period, color, garmenttype, garmentsubtype, atcshow, and bodytype are of the specified thing.
now on the second line, you're saying: OR, show me records where period, color, garmenttype, garmentsubtype, and atcshow are of the specified thing - with no regard to bodytype.

That's how it's working.

Does that help? Without addressing the entire logic in the whole query?

 

[M Costumes]

@Isaac Yeah..... when I added in the bit to display records with Null values (as not every field for every record will get filled in) it got a bit....large.

 

[M Costumes]

Generally speaking, if you want a criteria to "always apply to all scenarios", it needs to be repeated on each line of the criteria grid.
Let's just take the first 2 lines (to simplify!) ....

You're saying: show me records where: period, color, garmenttype, garmentsubtype, atcshow, and bodytype are of the specified thing.
now on the second line, you're saying: OR, show me records where period, color, garmenttype, garmentsubtype, and atcshow are of the specified thing - with no regard to bodytype.

That's how it's working.

Does that help? Without addressing the entire logic in the whole query?

I'm super new to this so I don't fully understand what you're saying. Than you for being patient with me. The user does not need to fill in every field to search. For example, they can just search for color and garment type, leave all the other cbo boxes blank, and it will return all records that contain those two criteria. If, a record contains any Null criteria, I still want it to show it to me. So if a record contains values for all criteria but one, I sill want it to show me that record if it meets the criteria specified in the search. Does that make sense?

 

[theDBguy]

I'm super new to this so I don't fully understand what you're saying. Than you for being patient with me. The user does not need to fill in every field to search. For example, they can just search for color and garment type, leave all the other cbo boxes blank, and it will return all records that contain those two criteria. If, a record contains any Null criteria, I still want it to show it to me. So if a record contains values for all criteria but one, I sill want it to show me that record if it meets the criteria specified in the search. Does that make sense?

Hi. May I suggest doing a search on "search forms" to find an example of what I mean by using VBA to create the filters. If I find a link, I'll post it too.

 

[Isaac]

I honestly don't use the method of queries that refer directly to forms, so I may have reached near the end of my helpfulness, but I definitely am used to putting criteria in the query grid so trying to help as best as I can - I think you don't need anywhere near as many vertical lines of criteria as you have. I think you just need to change stuff like this:

Like "*" & [Forms]![frmSearchInventory]![cboPeriod] & "*"

to this

Like "*" & [Forms]![frmSearchInventory]![cboPeriod] & "*" or is null

 

[M Costumes]

It appears things were just too messy and out of order. I made a new query, and was careful to put my parameters in order/together in the design grid, and now it's working again! Now to solve my next problem--but that's another thread! Ha!
Thank you, @theDBguy for recommending I look at VBA, for sure something I'll start dabbling with!

 

[Isaac]

Glad you got it working

 

[theDBguy]

It appears things were just too messy and out of order. I made a new query, and was careful to put my parameters in order/together in the design grid, and now it's working again! Now to solve my next problem--but that's another thread! Ha!
Thank you, @theDBguy for recommending I look at VBA, for sure something I'll start dabbling with!

Good luck with your project.