Selectively Apply Criteria

[ztpkng]

I have a field called "Part Number".



I have a form where someone can put in a part number and it can show all the results that match or where they can put in an * and find all results, except the results with * exclude Null, which is a problem.

What I want is for an entry of * to include all things INCLUDING Null values, but anything besides * to include only matches and exclude Null Values.

I had a criterion that looked like this: Like [forms]![Search].[FilterValue1]

I tried adding a second criterion that read like this: IIf([forms]![Search].[FilterValue1]<>"*",[forms]![Search].[Filtervalue1],Null) but this didn't work.

Any ideas on how I might accomplish this? Thanks!

[hapm]

add [forms]![Search].[FilterValue1] as a record field and uncheck its visibility. Then add an or condition where you check the field for being ="*". The resulting where condition would be: YourFieldsName LIKE [forms]![Search].[FilterValue1] OR [forms]![Search].[FilterValue1] = "*"

[ztpkng]

I'm sorry, perhaps I'm misunderstanding. It sounds like you are saying:

Create a new field like this ==> NewField: [forms]![Search].[FilterValue1]

Create Criteria for this NewField that are ==>
Like [forms]![Search].[FilterValue1] OR
[forms]![Search].[FilterValue1] = "*"

And have no criteria on the Part Number field.

Wouldn't that always return all results because NewField will always match the first criterion? Is it not confirming that it is Like itself?

[hapm]

Create Criteria for this NewField that are ==>
Like [forms]![Search].[FilterValue1] OR
[forms]![Search].[FilterValue1] = "*"

And have no criteria on the Part Number field.

Wouldn't that always return all results because NewField will always match the first criterion? Is it not confirming that it is Like itself?

You got me wrong on this one. The LIKE will be on the PartNumber field, and the = "*" on the new field but as an or condition.

[ztpkng]

Got it! Ok, that works.

So, can we make it more general? I actually have about 20 fields that will be searched at once. If many of these might be blank, I would need something like:

Part Number Like FilterValue1 and Part Size Like FilterValue2 OR
FilterValue1 = "*" and Part Size Like FilterValue2 OR
Part Number Like FilterValue1 and FilterValue2 = "*" OR
FilterValue1 = "*" and FilterValue2 = "*"

Obviously, this gets very cumbersome as it grows beyond just 1. Is there an alternative way to achieve the same result?

[hapm]

This will get very unhandy as long as you want to use the query designer, because the designer normalizes the conditions into OR form ((W AND X) OR (Y AND Z)). But for your use case the AND form ((W OR X) AND (Y OR Z)) would be much easier to handle. You can easily do that by switching to sql view and manipulate the WHERE condition directly. So you want get something like: WHERE ([Part Number] Like FilterValue1 OR FilterValue1="*") AND ([Part Size] Like FilterValue2 OR FilterValue2="*") AND ...

[ztpkng]

OH hey! I didn't know SQL view existed.

Well, that's handy. Sorry, I'm inexperienced in Access. I'll roll that in now. Thanks!