Hi there, For the last month, I have been struggling trying to create a query that will filter the correct information on a major form in my database. As it is a work project and features private information, I can only give a detailed description of what the Form and Query are currently set-up as (instead of pasting the SQL). Basically, there is a Form where the user clicks on the ID # combo box and Dlookup fills in 5 other combo boxes – Cbo1, cbo2, cbo3, cbo4, cbo5. They then hit the command button which pulls up all of the applicable “letters” (A, B, C) that pertain to that criteria – the macro behind this involves a temporary query, an append query, and a show results query. Below are the combo boxes I used with the applicable letters in brackets. Cbo 1- Apple (A)/Orange(B) Cbo2 – Celery(A)/Carrots(B)/Lettuce(C)/Tomatoes(D) Cbo3 – Yes(A)/No(B) (C,F,G) Cbo4 – Chicken(A)/Beef(B)/Pork(C) Cbo5 – New York(A)/Paris(B)/Tokyo(C)/London(D) The temporary query is where the filtering has normally taken place but now with the addition of more filters, I have received an error message saying that my “query is too complex”. My SQL statement is absolutely huge and definitely not simple. It features many “ANDS” and “ORS” and would probably be the equivalent of 5 word pages long! The reason it is so long is that each of these “letters” must only appear once (unique values is selected in the query properties) and if say Cbo1 pulls up “Apple” but Cbo3 pulls up “No” then I want the letters A, C, F, and G appearing in my final results. Similarly, if Cbo1 pulls up “Orange” and Cb2 pulls up Celery and Cb3 pulls up Yes, then the only letter I want appearing is “A”. Since I have unique values selected, I am not worried about duplicate letters being pulled up at the end. I basically just want the letters filtered in and/or out. The temporary query has been the only thing that I have modified and I have modified it to death, trying every different way of organization possible. I use the [Forms]![FormA]![control name] as the critiera (I just shortened it to Forms for the explanation) The filter would ideally work like this. We have 27 letters initially. And from there, we filter out first through Cbo1 AND Cbo2 AND CBo5. Next we filter out with Cbo3 (yes OR no). Then Cbo 4 (Chicken OR Beef OR pork). SQL Statement looks something like this WHERE FormsCbo1 AND FormsCbo2 AND Forms Cbo3 (Yes) OR Cbo4(Chicken) OR FormsCbo1 AND FormsCbo2 AND Forms Cbo3 (Yes) OR Cbo4(Beef) OR FormsCbo1 AND FormsCbo2 AND Forms Cbo3 (Yes) OR Cbo4(POrk )OR FormsCbo1 AND FormsCbo2 AND Forms Cbo3 (No) OR Cbo4(Chicken) etc. etc. Basically I want to just filter these 27 letters with the given criteria in the simplest way possible that doesn`t involve too complicated of a procedure as I am a beginner access user. This must be done in access and ideally, does not involve too many macros (against bosses wishes) and pulls up the correct results (without eliminating letters due to a null value in one of the other combos.). If the letter applies to AT LEAST one Cbo box selection, then I want it to show up. Similarly, if it applies to NONE of the cbo box selections, then I do want the letter to show up. I hope this made sense...it`s a bit complicated (I`ve probably overcomplicated it a bit) but I am very willing to try and explain it better. I really have been literally working on this for a month and felt like I had mastered it yesterday until I received the ``query is too complicated` error message. (Just an FYI...I have tried shortening all of the field, form, and control names with no luck) Any suggestions on how to tackle this monster??? Thank you in advance!
I think the best way to check the checkbox values and then forming the SQL would be through VBA Macros, since you have so many possible combinations.
