By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 2,753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Query by form seems to require certain inputs...

P: 114
Slightly related previous posts: (

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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM COMPILE, [MONTH], Market
  3. 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?
Oct 10 '07 #1
Share this Question
Share on Google+
1 Reply

Expert 2.5K+
P: 2,653
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 - "*".
Expand|Select|Wrap|Line Numbers
  1.  ..... COMPILE.AE Like Nz(Forms![Search Compile Form]!AE,"*") ....
P.S. Please would be nice.
Oct 10 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.