Re: Using combo box selection to supply criteria for query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Mr. B,
Sorry, I am still learning SQL and VBA, I don't think that I made the
module correctly. After I added the module none of the sql code
worked. Did I not need to make a module? Was the code supposed to go
somewhere else?

Steve and Karl -
Below is the SQL code that I currently have.
The form contains 5 combo boxes; SBT Account, Region, ADM, Month, Year
I named each of the combo boxes with the same names, just with
"Select" at the end, as you will see in the code.
I can leave all combo boxes blank and retrieve all 33 records, If I
use any one of the combo boxes individually, except for Region, the
query works fine. If I only make a selection in the Region combo box
then the qurey retrieves all 33 records instead of filtering by my
selection.

Thank you all again for the help.

Here is the code, sorry it is very long:

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)) 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) AND ((IsNull([Forms]![Main Form]![Region Select]))
<>False));



On Sep 16, 5:12 pm, KARL DEWEY <KARLDE...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
What part did not work?
Not return all records when no selection was made?
Post the query SQL.
--
Build a little, test a little.



"Erick C" wrote:
No luck.
I even tried putting the wildcard on both ends and it still did not
work:
Like "*" & [Forms]![Main Form]![Region Select] & "*"
and I tried:
Like [Forms]![Main Form]![Region Select] & "*"

And the Region is only a one letter designation coming from the select
query, so there is not a lot of stuff that is being brought into the
combo box, just single letters.

On Sep 16, 2:52 pm, KARL DEWEY <KARLDE...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
Try this --
Like [Forms]![YourFormName]![Combo22] & "*"

If nothing is selected in the combo then it is the same as selecing all.

--
Build a little, test a little.

"Erick C" wrote:
I am hoping someone can help me out with a problem that I am having..
I am setting up a database that will serve as an archive for our
reporting results. I am jsut starting to get everything together, so
I can modify anything if necessary. I made a form that has six
different combo boxes along the top. The data sources for the combo
boxes are different, some have names that I manually added. Other
boxes use select queries that I set up pulling data from certain
tables as data sources, since using the tables themselves yielded
duplicate results (the same region is listed multiple times in one
table).

In the end I want to set up buttons for various reports, and the
results will be driven by the combo box selections made on the forms.
I tried setting up a test select query, and it works for the most
part. 5 out of the 6 combo boxes will yield results if they are
selected by themselves, but one combo box will only filter the query
results when it is used in conjunction with another combo box.

It was pretty messed up setting up the query, since I had to set up a
line of criteria for each possible combination that the combo boxes
could be selected, or not selected, meaning I have 32 rows of
criteria. I can post my SQL view, but it is around 3 pages long whan
I copy it to Word

Any help I can get, or any suggestions on how I can set up my filters
so they will work better, more efficiently, I would be very grateful.

Erick- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -



On Sep 16, 5:12 pm, KARL DEWEY <KARLDE...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
What part did not work?
Not return all records when no selection was made?
Post the query SQL.
--
Build a little, test a little.



"Erick C" wrote:
No luck.
I even tried putting the wildcard on both ends and it still did not
work:
Like "*" & [Forms]![Main Form]![Region Select] & "*"
and I tried:
Like [Forms]![Main Form]![Region Select] & "*"

And the Region is only a one letter designation coming from the select
query, so there is not a lot of stuff that is being brought into the
combo box, just single letters.

On Sep 16, 2:52 pm, KARL DEWEY <KARLDE...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
Try this --
      Like [Forms]![YourFormName]![Combo22] & "*"

If nothing is selected in the combo then it is the same as selecing all.

--
Build a little, test a little.

"Erick C" wrote:
I am hoping someone can help me out with a problem that I am having..
I am setting up a database that will serve as an archive for our
reporting results.  I am jsut starting to get everything together, so
I can modify anything if necessary.  I made a form that has six
different combo boxes along the top.  The data sources for the combo
boxes are different, some have names that I manually added.  Other
boxes use select queries that I set up pulling data from certain
tables as data sources, since using the tables themselves yielded
duplicate results (the same region is listed multiple times in one
table).

In the end I want to set up buttons for various reports, and the
results will be driven by the combo box selections made on the forms.
I tried setting up a test select query, and it works for the most
part.  5 out of the 6 combo boxes will yield results if they are
selected by themselves, but one combo box will only filter the query
results when it is used in conjunction with another combo box.

It was pretty messed up setting up the query, since I had to set up a
line of criteria for each possible combination that the combo boxes
could be selected, or not selected, meaning I have 32 rows of
criteria.  I can post my SQL view, but it is around 3 pages long whan
I copy it to Word

Any help I can get, or any suggestions on how I can set up my filters
so they will work better, more efficiently, I would be very grateful.

Erick- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

.

Relevant Pages

  • RE: Designing Query - Help Please ASAP
    ... Once I copied the before mentioned langauge in my SQL view, ... query, ... SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing ... If I make a selection in the ProjectID box = then the subform will ...
    (microsoft.public.access.queries)
  • Re: Pass field value selected in Combo Box to SQL Query
    ... you would refer to the control in the query. ... The form must be open when the query executes. ... If this does not work for you, then you need to post the SQL of your queries so we can see what is happening. ... row selected in the combo box to be used as the selection criteria in a SQL ...
    (microsoft.public.access.gettingstarted)
  • Re: Tying check boxes to data
    ... which you could then join to your basic query. ... INNER JOIN tblCheckBoxes ... I have a form with a combo box based on a table that when I make a selection ... but I've never worked with check boxes before. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Pass field value selected in Combo Box to SQL Query
    ... And where would you use/display the results of this select query? ... using a combo box on your form to select filter criteria; ... box, and display all records, if there is no selection. ... My form displays a combo box populated by a SQL SELECT statement. ...
    (microsoft.public.access.gettingstarted)
  • Re: Requery Combo Box
    ... The SQL was generated like you said, ... On the same criteria row for each column add the restrictions ... The user selects a name from the first combo box that has a query behind ... After the selection, ...
    (microsoft.public.access.queries)