Query Criteria Help

[Subwind]

I have a query that I need to filter on 5 different field criteria. I can get this to nearly work, but it fails in one place.



The fields to query on are:

Customer, Post Code, County, Country, Account Manager

The customer field is required so can never be null, this filter works fine.

The Post Code field can be blank, as we have non-UK customers, this criteria does not work.

Like "*" & [Forms]![frmCustomerList]![txtPostCode] & "*" Or Is Null

I want the query to show all Customers, with or without a post code, but if a post code is input into the [txtPostCode], I want it to show only customers with this post code.

Can anyone suggest a better method?

I have a fully working version of the final list, but it is a query based on a sub query that is based on a sub query...... So, top level query is based on 5 sub queries all in all.

~Matt

[Subwind]

Would it help to have an Iif statement between the two "*" 's?

~Matt

[orange]

watch this free video tutorial for ideas
http://www.datapigtechnologies.com/f...earchform.html

[Subwind]

watch this free video tutorial for ideas
http://www.datapigtechnologies.com/f...earchform.html

Watched, but that doesn't help me when a customer has no value in the post code field.

Let's say I have 8 customers all starting with GEO. Now, if I only search in the customer field for GEO, all are shown. But, if 3 of these have no postcode, these will not appear when I activate any search on the customer name field.

If I do not have the "Or Is Null" at the end, it does not show any customers with no value in this field.

What I am after is for it to show all records, but if the criteria field is not blank, it only shows based on this input. By having "Or Is Null", it always displays customers with no post code, even if there is a post code specified in the criteria.

~Matt

[orange]

Please post the code involved.

Please elaborate on this

Let's say I have 8 customers all starting with GEO. Now, if I only search in the customer field for GEO, all are shown. But, if 3 of these have no postcode, these will not appear when I activate any search on the customer name field

.

What exactly do you want to select if there is NO POSTAL CODE??