Solved Parameter query not returning records with specific value

[M Costumes]

Hello! I'm a very beginning beginner with both Access and any kind of coding. I'm running into a rather interesting issue with a select query with parameters not returning records that contain a specific value, even though they should be returned. I've posted in another forum, and the feedback I've gotten thus far is that my SQL and everything seems like it should be working correctly. But it does not. So it was suggested I post here and share my file as well. So here goes!

I'm building a query (qrySearchInventory) to search tbl: Inventory. There are no lookups built into the table. The parameters can be entered via combo boxes on a form (frmSearchInventory) by the user. The row sources for the combo boxes are distinct values from the coordinating fields in tbl: Inventory. I want the user to be able to fill in any/all parameters and get results, so I'm using LIKE "*" in my criteria of each parameter. All values in the parameters are text. In tbl: Inventory the fields are also all text (except the PK which is an autonumber). This is my SQL for the query:

SELECT Inventory.InventoryID, Inventory.SetID, Inventory.ItemName, Inventory.Period, Inventory.Color, Inventory.GarmentType, Inventory.GarmentSubType,
     Inventory.ATCShow
FROM Inventory
WHERE (((Inventory.Period) Like "*" & [Forms]![frmSearchInventory]![cboPeriod] & "*")
     AND ((Inventory.Color) Like "*" & [Forms]![frmSearchInventory]![cboColor] & "*")
     AND ((Inventory.GarmentType) Like "*" & [Forms]![frmSearchInventory]![cboType] & "*")
     AND ((Inventory.GarmentSubType) Like "*" & [Forms]![frmSearchInventory]![cboSubType] & "*")
     AND ((Inventory.ATCShow) Like "*" & [Forms]![frmSearchInventory]![cboShow] & "*"));

Please excuse the excess brackets, as I said, very new so I'm letting Access do most of the writing for me.

For some reason, no matter what combination of parameters are used, it will not return any records where Inventory.GarmentType = suit coat, and the record also includes a SetID--even if the only parameter entered is "suit coat". For records that do not include a setID and are type=suit coat, the record is returned. If the record includes a setID and is not a suit coat, the record is returned. Not sure if that is related, as SetID is not a parameter, but it's something I noticed.

For example, if I enter "green" as the only parameter, it should return 4 records: a dress, a vest, pants, and a suit coat; but it only returns 3 (the suit coat is missing). The vest & the pants that it does return have the same SetID as the suit coat that is missing from the results.

My database currently contains only a small amount of dummy data just so I have something to play with/test things out with. So as of now, "suit coat" is the only value I'm aware of that will do this.

I've tried changing "*" to "%" and LIKE to ALIKE and no records at all were returned.

Any ideas? I've attached a copy of my file. Happy to provide any additional information. Thank you!

 

[pbaldy]

Records that have null in other fields won't match your criteria. Try:

Queries: Use a parameter to return all records if Null

 

[theDBguy]

Hi. Welcome to AWF!

I can't take a look at your file right now, but I would be willing to bet the issue is probably a matter or having either a Null or a ZLS in your data.

Edit: Oops, too slow... Welcome again!

 

[Isaac]

It's because the suitcoat has no SubType in the table. You can't compare like * to null

 

[M Costumes]

@pbaldy would I still need to include the LIKE statement with that? If so, would it be on both of them?
so it would read:
like "*"&[Forms]![myForm]![myControl]&"*" OR [Forms]![myForm]![myControl] Is Null

 

[pbaldy]

Yes, and it would be added to any field that may contain Null values. Looks like:

Like "*" & [Forms]![frmSearchInventory]![cboSubType] & "*" Or [Forms]![frmSearchInventory]![cboSubType] Is Null

Fair warning, the design grid will usually take that and make it look goofy, but it should still work.

 

[M Costumes]

@pbaldy Grand! It worked! Thank you so much! I've spent an absurd amount of time trying to figure that out--ha!

 

[pbaldy]

Happy to help! Think of Null as "unknown". Since Access doesn't know what the value is it can't decide whether it matches or not, so it ignores it.