If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Need help making query with criteria based on combo box select
Hi again.
Yes, the form name is Main_Form. I also use the same form and controls to create my filtered reports without error. Yes, I have the form open when I try viewing the query. Yes, I am getting the "Enter Parameter" for all of the controls. I tried to copy the sql and paste it into another query, but it yielded the same results. The closest that I have come to getting a query to do what I want it to looks pretty messy, but it may assist in identifying what is going wrong. With this sql I can use all of the combo boxes, but if I try to use the Region combo box by itself the query returns all records, nothing gets filtered. Funny part, if I use the Region in conjunction with another combo box then it filters as expected. I apologize in advance, the sql is very very long. I did not know of any way to get it done easier. Any advice you can provide would be greatly appreciated. SELECT ARCHIVE_ADM_Summary.ADM, ARCHIVE_ADM_Summary.Region, ARCHIVE_ADM_Summary.[SBT Account], ARCHIVE_ADM_Summary.Month, ARCHIVE_ADM_Summary.Year, ARCHIVE_ADM_Summary.[# Of Stores Counted], ARCHIVE_ADM_Summary.[ADM Cumulative Shrink %] FROM ARCHIVE_ADM_Summary WHERE (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select])) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]! [Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]! [Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)= [Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)= [Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]! [ADM Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]! [Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]! [Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((IsNull ([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]! [Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]! [Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]! [Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]! [Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.Month) =[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]! [ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select])) False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]! [Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)= [Forms]![Main_Form]![ADM Select]) AND ((IsNull([Forms]![Main_Form]! [SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select])) False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)); On Sep 28, 11:24*am, John Spencer wrote: Ok, so is the form open when you try to run the query. Is the form named Main_Form? Are the controls named exactly as you have entered them? *I'm not sure, but it is possible that having spaces in the control name is messing you up. *It has been so long since I've used spaces in control and form names, that I can't remember the effect that has. Also, are you getting Enter Parameter Value for every one of the controls? *If you are getting it for just one control what is the entire message? *IF you are getting the message for just one control then check the spelling on that control. If you are getting a prompt for none of the controls, then I suspect that you have that value in the filter or sort property of the query. *Often, the easiest way to clean up a 'ghost' parameter is to open the query up in SQL view, copy the SQL, and then paste it into a NEW query. *If the new query runs without the problem, then replace the old query with the new query. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Erick C wrote: Hi John - Thank you for your input. *I tried your version and I still have the issue of the "Enter Parameter Value" box opening for a value to be entered, even if I make selections on my form combo boxes. *I thought maybe it was happening because I made no combo box selections, but I disproved that theory... On Sep 28, 10:56 am, John Spencer wrote: You can USE = * *Or you can use LIKE as a comparison operator. *You cannot use both simultaneously. So the first step is to try: SELECT [ARCHIVE_ADM_Summary].* FROM [ARCHIVE_ADM_Summary] WHERE [ADM Select] Like [Forms]![Main_Form]![ADM Select] & "*" AND [Region Select] Like [Forms]![Main_Form]![Region Select] & "*" AND [SBT Account Select] Like [Forms]![Main_Form]![SBT Account Select] & "*" AND [Month Select] Like [Forms]![Main_Form]![Month Select] & "*" AND [Year Select] Like [Forms]![Main_Form]![Year Select] & "*"; John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County KARL DEWEY wrote: I do not see a problem but I also do not see where it would prompt for ADM.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#12
|
|||
|
|||
Need help making query with criteria based on combo box select
You can try opening the query in design view and click on Parameters to
define then by pasting [Forms]![Main_Form]![ADM Select] and the others in the window. -- Build a little, test a little. "Erick C" wrote: Hi again. Yes, the form name is Main_Form. I also use the same form and controls to create my filtered reports without error. Yes, I have the form open when I try viewing the query. Yes, I am getting the "Enter Parameter" for all of the controls. I tried to copy the sql and paste it into another query, but it yielded the same results. The closest that I have come to getting a query to do what I want it to looks pretty messy, but it may assist in identifying what is going wrong. With this sql I can use all of the combo boxes, but if I try to use the Region combo box by itself the query returns all records, nothing gets filtered. Funny part, if I use the Region in conjunction with another combo box then it filters as expected. I apologize in advance, the sql is very very long. I did not know of any way to get it done easier. Any advice you can provide would be greatly appreciated. SELECT ARCHIVE_ADM_Summary.ADM, ARCHIVE_ADM_Summary.Region, ARCHIVE_ADM_Summary.[SBT Account], ARCHIVE_ADM_Summary.Month, ARCHIVE_ADM_Summary.Year, ARCHIVE_ADM_Summary.[# Of Stores Counted], ARCHIVE_ADM_Summary.[ADM Cumulative Shrink %] FROM ARCHIVE_ADM_Summary WHERE (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select])) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]! [Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]! [Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)= [Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)= [Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]! [ADM Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]! [Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]! [Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((IsNull ([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]! [Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]! [Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]! [Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]! [Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.Month) =[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]! [ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select])) False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]! [Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)= [Forms]![Main_Form]![ADM Select]) AND ((IsNull([Forms]![Main_Form]! [SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select])) False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)); On Sep 28, 11:24 am, John Spencer wrote: Ok, so is the form open when you try to run the query. Is the form named Main_Form? Are the controls named exactly as you have entered them? I'm not sure, but it is possible that having spaces in the control name is messing you up. It has been so long since I've used spaces in control and form names, that I can't remember the effect that has. Also, are you getting Enter Parameter Value for every one of the controls? If you are getting it for just one control what is the entire message? IF you are getting the message for just one control then check the spelling on that control. If you are getting a prompt for none of the controls, then I suspect that you have that value in the filter or sort property of the query. Often, the easiest way to clean up a 'ghost' parameter is to open the query up in SQL view, copy the SQL, and then paste it into a NEW query. If the new query runs without the problem, then replace the old query with the new query. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Erick C wrote: Hi John - Thank you for your input. I tried your version and I still have the issue of the "Enter Parameter Value" box opening for a value to be entered, even if I make selections on my form combo boxes. I thought maybe it was happening because I made no combo box selections, but I disproved that theory... On Sep 28, 10:56 am, John Spencer wrote: You can USE = Or you can use LIKE as a comparison operator. You cannot use both simultaneously. So the first step is to try: SELECT [ARCHIVE_ADM_Summary].* FROM [ARCHIVE_ADM_Summary] WHERE [ADM Select] Like [Forms]![Main_Form]![ADM Select] & "*" AND [Region Select] Like [Forms]![Main_Form]![Region Select] & "*" AND [SBT Account Select] Like [Forms]![Main_Form]![SBT Account Select] & "*" AND [Month Select] Like [Forms]![Main_Form]![Month Select] & "*" AND [Year Select] Like [Forms]![Main_Form]![Year Select] & "*"; John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County KARL DEWEY wrote: I do not see a problem but I also do not see where it would prompt for ADM.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#13
|
|||
|
|||
Need help making query with criteria based on combo box select
Hi Karl -
Everything looks fine when I open the query in design view. All of the criteria appears to be correct, which is why I can't seem to figure out what is going on.... On Sep 28, 12:17*pm, KARL DEWEY wrote: You can try opening the query in design view and click on Parameters to define then by pasting * *[Forms]![Main_Form]![ADM Select] and the others in the window. -- Build a little, test a little. "Erick C" wrote: Hi again. Yes, the form name is Main_Form. *I also use the same form and controls to create my filtered reports without error. Yes, I have the form open when I try viewing the query. Yes, I am getting the "Enter Parameter" for all of the controls. I tried to copy the sql and paste it into another query, but it yielded the same results. The closest that I have come to getting a query to do what I want it to looks pretty messy, but it may assist in identifying what is going wrong. *With this sql I can use all of the combo boxes, but if I try to use the Region combo box by itself the query returns all records, nothing gets filtered. *Funny part, if I use the Region in conjunction with another combo box then it filters as expected. I apologize in advance, the sql is very very long. *I did not know of any way to get it done easier. *Any advice you can provide would be greatly appreciated. SELECT ARCHIVE_ADM_Summary.ADM, ARCHIVE_ADM_Summary.Region, ARCHIVE_ADM_Summary.[SBT Account], ARCHIVE_ADM_Summary.Month, ARCHIVE_ADM_Summary.Year, ARCHIVE_ADM_Summary.[# Of Stores Counted], ARCHIVE_ADM_Summary.[ADM Cumulative Shrink %] FROM ARCHIVE_ADM_Summary WHERE (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select])) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]! [Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]! [Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)= [Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)= [Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]! [ADM Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]! [Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]! [Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((IsNull ([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]! [Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]! [Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]! [Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ... read more »- Hide quoted text - - Show quoted text - |
#14
|
|||
|
|||
Need help making query with criteria based on combo box select
Post back what you have in SQL now.
What kind of fields are [ADM Select], [Region Select], [SBT Account Select], [Month Select], and [Year Select] that you believed you could use LIKE with them? Post sample data as to what is in [Month Select] and [Year Select]. -- Build a little, test a little. "Erick C" wrote: Hi Karl - Everything looks fine when I open the query in design view. All of the criteria appears to be correct, which is why I can't seem to figure out what is going on.... On Sep 28, 12:17 pm, KARL DEWEY wrote: You can try opening the query in design view and click on Parameters to define then by pasting [Forms]![Main_Form]![ADM Select] and the others in the window. -- Build a little, test a little. "Erick C" wrote: Hi again. Yes, the form name is Main_Form. I also use the same form and controls to create my filtered reports without error. Yes, I have the form open when I try viewing the query. Yes, I am getting the "Enter Parameter" for all of the controls. I tried to copy the sql and paste it into another query, but it yielded the same results. The closest that I have come to getting a query to do what I want it to looks pretty messy, but it may assist in identifying what is going wrong. With this sql I can use all of the combo boxes, but if I try to use the Region combo box by itself the query returns all records, nothing gets filtered. Funny part, if I use the Region in conjunction with another combo box then it filters as expected. I apologize in advance, the sql is very very long. I did not know of any way to get it done easier. Any advice you can provide would be greatly appreciated. SELECT ARCHIVE_ADM_Summary.ADM, ARCHIVE_ADM_Summary.Region, ARCHIVE_ADM_Summary.[SBT Account], ARCHIVE_ADM_Summary.Month, ARCHIVE_ADM_Summary.Year, ARCHIVE_ADM_Summary.[# Of Stores Counted], ARCHIVE_ADM_Summary.[ADM Cumulative Shrink %] FROM ARCHIVE_ADM_Summary WHERE (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select])) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]! [Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]! [Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)= [Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)= [Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]! [ADM Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]! [Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]! [Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((IsNull ([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]! [Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]! [Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]! [Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ... read more »- Hide quoted text - - Show quoted text - |
#15
|
|||
|
|||
Need help making query with criteria based on combo box select
Karl -
I have two separate test queries. One has SQL that exactly matches: SELECT [ARCHIVE_ADM_Summary].* FROM [ARCHIVE_ADM_Summary] WHERE [ADM Select] Like [Forms]![Main_Form]![ADM Select] & "*" AND [Region Select] Like [Forms]![Main_Form]![Region Select] & "*" AND [SBT Account Select] Like [Forms]![Main_Form]![SBT Account Select] & "*" AND [Month Select] Like [Forms]![Main_Form]![Month Select] & "*" AND [Year Select] Like [Forms]![Main_Form]![Year Select] & "*"; My second test has the long SQL listed in my previous posting. [Month Select] and [Year Select] are both Numbers (8, 12, 2009, etc...) All other combo boxes are Text fields, all text, no alpha numeric mix. On Sep 28, 3:32*pm, KARL DEWEY wrote: Post back what you have in SQL now. What kind of fields are *[ADM Select], [Region Select], [SBT Account Select], *[Month Select], and [Year Select] that you believed you could use LIKE with them? Post sample data as to what is in [Month Select] and [Year Select]. -- Build a little, test a little. "Erick C" wrote: Hi Karl - Everything looks fine when I open the query in design view. *All of the criteria appears to be correct, which is why I can't seem to figure out what is going on.... On Sep 28, 12:17 pm, KARL DEWEY wrote: You can try opening the query in design view and click on Parameters to define then by pasting * *[Forms]![Main_Form]![ADM Select] and the others in the window. -- Build a little, test a little. "Erick C" wrote: Hi again. Yes, the form name is Main_Form. *I also use the same form and controls to create my filtered reports without error. Yes, I have the form open when I try viewing the query. Yes, I am getting the "Enter Parameter" for all of the controls. I tried to copy the sql and paste it into another query, but it yielded the same results. The closest that I have come to getting a query to do what I want it to looks pretty messy, but it may assist in identifying what is going wrong. *With this sql I can use all of the combo boxes, but if I try to use the Region combo box by itself the query returns all records, nothing gets filtered. *Funny part, if I use the Region in conjunction with another combo box then it filters as expected. I apologize in advance, the sql is very very long. *I did not know of any way to get it done easier. *Any advice you can provide would be greatly appreciated. SELECT ARCHIVE_ADM_Summary.ADM, ARCHIVE_ADM_Summary.Region, ARCHIVE_ADM_Summary.[SBT Account], ARCHIVE_ADM_Summary.Month, ARCHIVE_ADM_Summary.Year, ARCHIVE_ADM_Summary.[# Of Stores Counted], ARCHIVE_ADM_Summary.[ADM Cumulative Shrink %] FROM ARCHIVE_ADM_Summary WHERE (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select])) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]! [Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]! [Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)= [Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)= [Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]! [ADM Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]! [Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]! [Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((IsNull ([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]! [Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]! [Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]! [Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ... read more »- Hide quoted text - - Show quoted text - |
#16
|
|||
|
|||
Need help making query with criteria based on combo box select
I would try one more query with just these criteria to see results --
SELECT [ARCHIVE_ADM_Summary].* FROM [ARCHIVE_ADM_Summary] WHERE [ARCHIVE_ADM_Summary].[Month Select] = [Forms]![Main_Form]![Month Select] AND [ARCHIVE_ADM_Summary].[Year Select] = [Forms]![Main_Form]![Year Select]; If that works then add a little at a time. Note that I included the table/query name with the [Year Select] and [Month Select]. -- Build a little, test a little. "Erick C" wrote: Karl - I have two separate test queries. One has SQL that exactly matches: SELECT [ARCHIVE_ADM_Summary].* FROM [ARCHIVE_ADM_Summary] WHERE [ADM Select] Like [Forms]![Main_Form]![ADM Select] & "*" AND [Region Select] Like [Forms]![Main_Form]![Region Select] & "*" AND [SBT Account Select] Like [Forms]![Main_Form]![SBT Account Select] & "*" AND [Month Select] Like [Forms]![Main_Form]![Month Select] & "*" AND [Year Select] Like [Forms]![Main_Form]![Year Select] & "*"; My second test has the long SQL listed in my previous posting. [Month Select] and [Year Select] are both Numbers (8, 12, 2009, etc...) All other combo boxes are Text fields, all text, no alpha numeric mix. On Sep 28, 3:32 pm, KARL DEWEY wrote: Post back what you have in SQL now. What kind of fields are [ADM Select], [Region Select], [SBT Account Select], [Month Select], and [Year Select] that you believed you could use LIKE with them? Post sample data as to what is in [Month Select] and [Year Select]. -- Build a little, test a little. "Erick C" wrote: Hi Karl - Everything looks fine when I open the query in design view. All of the criteria appears to be correct, which is why I can't seem to figure out what is going on.... On Sep 28, 12:17 pm, KARL DEWEY wrote: You can try opening the query in design view and click on Parameters to define then by pasting [Forms]![Main_Form]![ADM Select] and the others in the window. -- Build a little, test a little. "Erick C" wrote: Hi again. Yes, the form name is Main_Form. I also use the same form and controls to create my filtered reports without error. Yes, I have the form open when I try viewing the query. Yes, I am getting the "Enter Parameter" for all of the controls. I tried to copy the sql and paste it into another query, but it yielded the same results. The closest that I have come to getting a query to do what I want it to looks pretty messy, but it may assist in identifying what is going wrong. With this sql I can use all of the combo boxes, but if I try to use the Region combo box by itself the query returns all records, nothing gets filtered. Funny part, if I use the Region in conjunction with another combo box then it filters as expected. I apologize in advance, the sql is very very long. I did not know of any way to get it done easier. Any advice you can provide would be greatly appreciated. SELECT ARCHIVE_ADM_Summary.ADM, ARCHIVE_ADM_Summary.Region, ARCHIVE_ADM_Summary.[SBT Account], ARCHIVE_ADM_Summary.Month, ARCHIVE_ADM_Summary.Year, ARCHIVE_ADM_Summary.[# Of Stores Counted], ARCHIVE_ADM_Summary.[ADM Cumulative Shrink %] FROM ARCHIVE_ADM_Summary WHERE (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select])) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]! [Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]! [Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)= [Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)= [Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]! [ADM Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]! [Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]! [Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((IsNull ([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]! [Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]! [Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]! [Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False) AND ((IsNull([Forms]![Main_Form]![Region Select]))False)) OR (((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ... read more »- Hide quoted text - - Show quoted text - |
#17
|
|||
|
|||
Need help making query with criteria based on combo box select
Hi Karl -
This query returns results only when both combo boxes are being used. If I make a selection in month and not in year, vice versa, or make no selection at all, then no records are returned. I have to be able to use just one combo box independently, or no combo boxes at all and still have records returned. On Sep 28, 4:34*pm, KARL DEWEY wrote: I would try one more query with just these criteria to see results -- SELECT [ARCHIVE_ADM_Summary].* FROM [ARCHIVE_ADM_Summary] WHERE [ARCHIVE_ADM_Summary].[Month Select] = [Forms]![Main_Form]![Month Select] AND [ARCHIVE_ADM_Summary].[Year Select] = [Forms]![Main_Form]![Year Select]; If that works then add a little at a time. * Note that I included the table/query name with the [Year Select] and [Month Select]. -- Build a little, test a little. "Erick C" wrote: Karl - I have two separate test queries. *One has SQL that exactly matches: SELECT [ARCHIVE_ADM_Summary].* FROM [ARCHIVE_ADM_Summary] WHERE [ADM Select] Like [Forms]![Main_Form]![ADM Select] & "*" AND [Region Select] Like [Forms]![Main_Form]![Region Select] & "*" AND [SBT Account Select] Like [Forms]![Main_Form]![SBT Account Select] & "*" AND [Month Select] Like [Forms]![Main_Form]![Month Select] & "*" AND [Year Select] Like [Forms]![Main_Form]![Year Select] & "*"; My second test has the long SQL listed in my previous posting. [Month Select] and [Year Select] are both Numbers (8, 12, 2009, etc...) All other combo boxes are Text fields, all text, no alpha numeric mix. On Sep 28, 3:32 pm, KARL DEWEY wrote: Post back what you have in SQL now. What kind of fields are *[ADM Select], [Region Select], [SBT Account Select], *[Month Select], and [Year Select] that you believed you could use LIKE with them? Post sample data as to what is in [Month Select] and [Year Select]. -- Build a little, test a little. "Erick C" wrote: Hi Karl - Everything looks fine when I open the query in design view. *All of the criteria appears to be correct, which is why I can't seem to figure out what is going on.... On Sep 28, 12:17 pm, KARL DEWEY wrote: You can try opening the query in design view and click on Parameters to define then by pasting * *[Forms]![Main_Form]![ADM Select] and the others in the window. -- Build a little, test a little. "Erick C" wrote: Hi again. Yes, the form name is Main_Form. *I also use the same form and controls to create my filtered reports without error. Yes, I have the form open when I try viewing the query. Yes, I am getting the "Enter Parameter" for all of the controls.. I tried to copy the sql and paste it into another query, but it yielded the same results. The closest that I have come to getting a query to do what I want it to looks pretty messy, but it may assist in identifying what is going wrong. *With this sql I can use all of the combo boxes, but if I try to use the Region combo box by itself the query returns all records, nothing gets filtered. *Funny part, if I use the Region in conjunction with another combo box then it filters as expected. I apologize in advance, the sql is very very long. *I did not know of any way to get it done easier. *Any advice you can provide would be greatly appreciated. SELECT ARCHIVE_ADM_Summary.ADM, ARCHIVE_ADM_Summary.Region, ARCHIVE_ADM_Summary.[SBT Account], ARCHIVE_ADM_Summary.Month, ARCHIVE_ADM_Summary.Year, ARCHIVE_ADM_Summary.[# Of Stores Counted], ARCHIVE_ADM_Summary.[ADM Cumulative Shrink %] FROM ARCHIVE_ADM_Summary WHERE (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select])) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]! [Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]! [Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)= [Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)= [Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]! [ADM Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]! [Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]! [Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((IsNull ([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]! [Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]! [Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]! [Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ... read more »- Hide quoted text - - Show quoted text - |
#18
|
|||
|
|||
Need help making query with criteria based on combo box select
Here --
SELECT [ARCHIVE_ADM_Summary].* FROM [ARCHIVE_ADM_Summary] WHERE [ARCHIVE_ADM_Summary].[Month Select] Like [Forms]![Main_Form]![Month Select] & "*" AND [ARCHIVE_ADM_Summary].[Year Select] Like [Forms]![Main_Form]![Year Select] & "*"; -- Build a little, test a little. "Erick C" wrote: Hi Karl - This query returns results only when both combo boxes are being used. If I make a selection in month and not in year, vice versa, or make no selection at all, then no records are returned. I have to be able to use just one combo box independently, or no combo boxes at all and still have records returned. On Sep 28, 4:34 pm, KARL DEWEY wrote: I would try one more query with just these criteria to see results -- SELECT [ARCHIVE_ADM_Summary].* FROM [ARCHIVE_ADM_Summary] WHERE [ARCHIVE_ADM_Summary].[Month Select] = [Forms]![Main_Form]![Month Select] AND [ARCHIVE_ADM_Summary].[Year Select] = [Forms]![Main_Form]![Year Select]; If that works then add a little at a time. Note that I included the table/query name with the [Year Select] and [Month Select]. -- Build a little, test a little. "Erick C" wrote: Karl - I have two separate test queries. One has SQL that exactly matches: SELECT [ARCHIVE_ADM_Summary].* FROM [ARCHIVE_ADM_Summary] WHERE [ADM Select] Like [Forms]![Main_Form]![ADM Select] & "*" AND [Region Select] Like [Forms]![Main_Form]![Region Select] & "*" AND [SBT Account Select] Like [Forms]![Main_Form]![SBT Account Select] & "*" AND [Month Select] Like [Forms]![Main_Form]![Month Select] & "*" AND [Year Select] Like [Forms]![Main_Form]![Year Select] & "*"; My second test has the long SQL listed in my previous posting. [Month Select] and [Year Select] are both Numbers (8, 12, 2009, etc...) All other combo boxes are Text fields, all text, no alpha numeric mix. On Sep 28, 3:32 pm, KARL DEWEY wrote: Post back what you have in SQL now. What kind of fields are [ADM Select], [Region Select], [SBT Account Select], [Month Select], and [Year Select] that you believed you could use LIKE with them? Post sample data as to what is in [Month Select] and [Year Select]. -- Build a little, test a little. "Erick C" wrote: Hi Karl - Everything looks fine when I open the query in design view. All of the criteria appears to be correct, which is why I can't seem to figure out what is going on.... On Sep 28, 12:17 pm, KARL DEWEY wrote: You can try opening the query in design view and click on Parameters to define then by pasting [Forms]![Main_Form]![ADM Select] and the others in the window. -- Build a little, test a little. "Erick C" wrote: Hi again. Yes, the form name is Main_Form. I also use the same form and controls to create my filtered reports without error. Yes, I have the form open when I try viewing the query. Yes, I am getting the "Enter Parameter" for all of the controls.. I tried to copy the sql and paste it into another query, but it yielded the same results. The closest that I have come to getting a query to do what I want it to looks pretty messy, but it may assist in identifying what is going wrong. With this sql I can use all of the combo boxes, but if I try to use the Region combo box by itself the query returns all records, nothing gets filtered. Funny part, if I use the Region in conjunction with another combo box then it filters as expected. I apologize in advance, the sql is very very long. I did not know of any way to get it done easier. Any advice you can provide would be greatly appreciated. SELECT ARCHIVE_ADM_Summary.ADM, ARCHIVE_ADM_Summary.Region, ARCHIVE_ADM_Summary.[SBT Account], ARCHIVE_ADM_Summary.Month, ARCHIVE_ADM_Summary.Year, ARCHIVE_ADM_Summary.[# Of Stores Counted], ARCHIVE_ADM_Summary.[ADM Cumulative Shrink %] FROM ARCHIVE_ADM_Summary WHERE (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select])) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]! [Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]! [Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)= [Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)= [Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]! [ADM Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]! [Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]! [Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((IsNull ([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]! [Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]! [Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]! [Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ... read more »- Hide quoted text - - Show quoted text - |
#19
|
|||
|
|||
Need help making query with criteria based on combo box select
Hi Karl -
OK, made the changes. I went back and tried adding the different combo boxes one by one and it looks like the query is working right! Thank you again for all of the help! On Sep 29, 9:38*am, KARL DEWEY wrote: Here -- SELECT [ARCHIVE_ADM_Summary].* FROM [ARCHIVE_ADM_Summary] WHERE [ARCHIVE_ADM_Summary].[Month Select] Like [Forms]![Main_Form]![Month Select] & "*" AND [ARCHIVE_ADM_Summary].[Year Select] Like [Forms]![Main_Form]![Year Select] & "*"; -- Build a little, test a little. "Erick C" wrote: Hi Karl - This query returns results only when both combo boxes are being used. If I make a selection in month and not in year, vice versa, or make no selection at all, then no records are returned. *I have to be able to use just one combo box independently, or no combo boxes at all and still have records returned. On Sep 28, 4:34 pm, KARL DEWEY wrote: I would try one more query with just these criteria to see results -- SELECT [ARCHIVE_ADM_Summary].* FROM [ARCHIVE_ADM_Summary] WHERE [ARCHIVE_ADM_Summary].[Month Select] = [Forms]![Main_Form]![Month Select] AND [ARCHIVE_ADM_Summary].[Year Select] = [Forms]![Main_Form]![Year Select]; If that works then add a little at a time. * Note that I included the table/query name with the [Year Select] and [Month Select]. -- Build a little, test a little. "Erick C" wrote: Karl - I have two separate test queries. *One has SQL that exactly matches: SELECT [ARCHIVE_ADM_Summary].* FROM [ARCHIVE_ADM_Summary] WHERE [ADM Select] Like [Forms]![Main_Form]![ADM Select] & "*" AND [Region Select] Like [Forms]![Main_Form]![Region Select] & "*" AND [SBT Account Select] Like [Forms]![Main_Form]![SBT Account Select] & "*" AND [Month Select] Like [Forms]![Main_Form]![Month Select] & "*" AND [Year Select] Like [Forms]![Main_Form]![Year Select] & "*"; My second test has the long SQL listed in my previous posting. [Month Select] and [Year Select] are both Numbers (8, 12, 2009, etc...) All other combo boxes are Text fields, all text, no alpha numeric mix. On Sep 28, 3:32 pm, KARL DEWEY wrote: Post back what you have in SQL now. What kind of fields are *[ADM Select], [Region Select], [SBT Account Select], *[Month Select], and [Year Select] that you believed you could use LIKE with them? Post sample data as to what is in [Month Select] and [Year Select]. -- Build a little, test a little. "Erick C" wrote: Hi Karl - Everything looks fine when I open the query in design view. *All of the criteria appears to be correct, which is why I can't seem to figure out what is going on.... On Sep 28, 12:17 pm, KARL DEWEY wrote: You can try opening the query in design view and click on Parameters to define then by pasting * *[Forms]![Main_Form]![ADM Select] and the others in the window. -- Build a little, test a little. "Erick C" wrote: Hi again. Yes, the form name is Main_Form. *I also use the same form and controls to create my filtered reports without error. Yes, I have the form open when I try viewing the query. Yes, I am getting the "Enter Parameter" for all of the controls.. I tried to copy the sql and paste it into another query, but it yielded the same results. The closest that I have come to getting a query to do what I want it to looks pretty messy, but it may assist in identifying what is going wrong. *With this sql I can use all of the combo boxes, but if I try to use the Region combo box by itself the query returns all records, nothing gets filtered. *Funny part, if I use the Region in conjunction with another combo box then it filters as expected. I apologize in advance, the sql is very very long. *I did not know of any way to get it done easier. *Any advice you can provide would be greatly appreciated. SELECT ARCHIVE_ADM_Summary.ADM, ARCHIVE_ADM_Summary.Region, ARCHIVE_ADM_Summary.[SBT Account], ARCHIVE_ADM_Summary.Month, ARCHIVE_ADM_Summary.Year, ARCHIVE_ADM_Summary.[# Of Stores Counted], ARCHIVE_ADM_Summary.[ADM Cumulative Shrink %] FROM ARCHIVE_ADM_Summary WHERE (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select])) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]! [Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]! [Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary..Month)= [Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)= [Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]! [ADM Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]! [Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]! [Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]! [Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]! [Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])= [Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]! [Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Month Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]! [Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.[SBT Account])=[Forms]![Main_Form]![SBT Account Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select])) False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((IsNull([Forms]![Main_Form]![SBT Account Select]))False) AND ((IsNull([Forms]![Main_Form]![Month Select]))False) AND ((IsNull ([Forms]![Main_Form]![Year Select]))False)) OR (((ARCHIVE_ADM_Summary.Region)=[Forms]![Main_Form]![Region Select]) AND ((ARCHIVE_ADM_Summary.Month)=[Forms]![Main_Form]![Month Select]) AND ((ARCHIVE_ADM_Summary.Year)=[Forms]![Main_Form]![Year Select]) AND ((IsNull([Forms]![Main_Form]![ADM Select]))False) AND ((IsNull ([Forms]![Main_Form]![SBT Account Select]))False)) OR (((ARCHIVE_ADM_Summary.ADM)=[Forms]![Main_Form]![ADM Select]) AND ... read more »- Hide quoted text - - Show quoted text - |
|
Thread Tools | |
Display Modes | |
|
|