Query by form seems to require certain inputs...

[martin DH]

Slightly related previous posts: (http://www.thescripts.com/forum/thread720001.html)
and
(http://www.thescripts.com/forum/thread708234.html).

Okay, I've had to go back the drawing board a couple of times on this one (it's all based on an data import of which I don't have control).

My new problems is, I think, in my SQL.

I have a form (Search Compile Form) that provides the criteria for a query (Search Compile Query) that answers back with the appropriately filtered data.
My problem is that it seems as though certain fields must be filled in on the form or incorrect results come back (NAC or AE for example). I don't want any required fields on the form and I can't quite see what it acts this way
Here's the query:

SELECT COMPILE.Compile_ID, COMPILE.Results_ID, MONTH.Month, COMPILE.Year, Market.Market, COMPILE.ClientID, COMPILE.ClientName, COMPILE.AE, COMPILE.NAC, COMPILE.SalesPerson, COMPILE.SalesManager, COMPILE.ResponseNo, COMPILE.EmailAddress, COMPILE.Sales_exp, COMPILE.Sales_prof, COMPILE.Sales_ability, COMPILE.Sales_know, COMPILE.Sales_expectations, COMPILE.Sales_contact, COMPILE.AE_exp, COMPILE.AE_effective, COMPILE.AE_know, COMPILE.AE_ability, COMPILE.NAC_exp, COMPILE.NAC_time, COMPILE.NAC_avail, COMPILE.NAC_know, COMPILE.Payroll_accuracy, COMPILE.Client_informed, COMPILE.AE_contact, COMPILE.NAC_contact, COMPILE.Client_conversion, COMPILE.Client_service, COMPILE.Client_satis, COMPILE.Client_recommend, COMPILE.Client_comments
FROM COMPILE, [MONTH], Market
WHERE (COMPILE.Month=MONTH.Month_ID) AND (COMPILE.MarketID=Market.MarketID) AND ((Month.Month) Like (Forms![Search Compile Form]!Month)) AND ((COMPILE.Year) Like (Forms![Search Compile Form]!Year)) AND (((Market.MarketID) Like IIf(Forms![Search Compile Form]!SMCkBox=-1,1,"")) Or ((Market.MarketID) Like IIf(Forms![Search Compile Form]!MMCkBox=-1,2,""))) AND (((COMPILE.NAC) Like (Forms![Search Compile Form]!NAC)) Or ((COMPILE.AE) Like (Forms![Search Compile Form]!AE)));

To help clarify, by example: if, in the form, I enter data for month, year, and AE, and check the boxes for market - I get accurate results. If I enter data for month, year, and check the boxes for market, but leave AE blank - instead of returning results for the entered criteria, I get nothing. Or again, if I check teh boxes for market, and enter the year - instead of getting all results for those markets and that year, I get nothing.
Any ideas?

[FishVal]

Hi, martin.

If you want to treat the situation when form field is empty as "no filtering required", then you may use Nz function to substitute Null criteria with All - "*".

 ..... COMPILE.AE Like Nz(Forms![Search Compile Form]!AE,"*") ....
 

P.S. Please would be nice.