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?
