Need help making query with criteria based on combo box selections

[Erick C]

Hi everybody -
I have a problem that I am hoping someone may be able to help me out
with. I have a form in my database with 5 different combo boxes. I
got some help making a report that is created using VBA that brings in
data based off of the combo box selections, even if nothing is
selected. What I am hoping to create is a select query that works in
the same way, so basically I can have two buttons on my form; one
button to create a report and another button that will open the select
query allowing the user to copy the info and paste it into Excel.
I have tried a few times to make a query but none of them have worked
well. The closest that I came to success had around 36 lines of
criteria (one for every combo box selection possibility) and it was
around 4 pages long in SQL view.
I am hoping someone can help me to get the query working, or if there
is an easier way to get to my goal then I can create whatever is
necessary.
Any help that I can get would be greatly appreciated.

Just in case, my table name is [ARCHIVE_ADM_Summary]
My combo boxes are
[Forms]![Main_Form]![ADM Select]
[Forms]![Main_Form]![Region Select]
[Forms]![Main_Form]![SBT Account Select]
[Forms]![Main_Form]![Month Select]
[Forms]![Main_Form]![Year Select]

 

[KARL DEWEY]

Try this --
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] & "*";

 

[Erick C]

Hi Karl -
Thanks for your reply. I tried to use the sql that you provided but I
received a "Syntax error (missing operator) in query expression..."
error. When I press OK it highlights "Like" in the sql statement.
For fun, I tried removing the "Like" statements and view but a message
box opens asking me to enter a value for ADM.

Thanks for your assistance.

Try this --
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] & "*";

--
Build a little, test a little.

Hi everybody -
I have a problem that I am hoping someone may be able to help me out
with.  I have a form in my database with 5 different combo boxes.  I
got some help making a report that is created using VBA that brings in
data based off of the combo box selections, even if nothing is
selected.  What I am hoping to create is a select query that works in
the same way, so basically I can have two buttons on my form; one
button to create a report and another button that will open the select
query allowing the user to copy the info and paste it into Excel.
I have tried a few times to make a query but none of them have worked
well.  The closest that I came to success had around 36 lines of
criteria (one for every combo box selection possibility) and it was
around 4 pages long in SQL view.
I am hoping someone can help me to get the query working, or if there
is an easier way to get to my goal then I can create whatever is
necessary.
Any help that I can get would be greatly appreciated.

Just in case, my table name is [ARCHIVE_ADM_Summary]
My combo boxes are
[Forms]![Main_Form]![ADM Select]
[Forms]![Main_Form]![Region Select]
[Forms]![Main_Form]![SBT Account Select]
[Forms]![Main_Form]![Month Select]
[Forms]![Main_Form]![Year Select]- Hide quoted text -

- Show quoted text -

 

[KARL DEWEY]

Apparently something messed up in the copying, posting, copying, and then
pasting.
Copy what you wound up with in your query and post back so I can look at it.

--
Build a little, test a little.

Hi Karl -
Thanks for your reply. I tried to use the sql that you provided but I
received a "Syntax error (missing operator) in query expression..."
error. When I press OK it highlights "Like" in the sql statement.
For fun, I tried removing the "Like" statements and view but a message
box opens asking me to enter a value for ADM.

Thanks for your assistance.

Try this --
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] & "*";

--
Build a little, test a little.

Hi everybody -
I have a problem that I am hoping someone may be able to help me out
with. I have a form in my database with 5 different combo boxes. I
got some help making a report that is created using VBA that brings in
data based off of the combo box selections, even if nothing is
selected. What I am hoping to create is a select query that works in
the same way, so basically I can have two buttons on my form; one
button to create a report and another button that will open the select
query allowing the user to copy the info and paste it into Excel.
I have tried a few times to make a query but none of them have worked
well. The closest that I came to success had around 36 lines of
criteria (one for every combo box selection possibility) and it was
around 4 pages long in SQL view.
I am hoping someone can help me to get the query working, or if there
is an easier way to get to my goal then I can create whatever is
necessary.
Any help that I can get would be greatly appreciated.

Just in case, my table name is [ARCHIVE_ADM_Summary]
My combo boxes are
[Forms]![Main_Form]![ADM Select]
[Forms]![Main_Form]![Region Select]
[Forms]![Main_Form]![SBT Account Select]
[Forms]![Main_Form]![Month Select]
[Forms]![Main_Form]![Year Select]- Hide quoted text -

- Show quoted text -

 

[Erick C]

No problem.
Here it is:

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] & "*";

Apparently something messed up in the copying, posting, copying, and then
pasting.
Copy what you wound up with in your query and post back so I can look at it.

--
Build a little, test a little.

Hi Karl -
Thanks for your reply.  I tried to use the sql that you provided but I
received a "Syntax error (missing operator) in query expression..."
error.  When I press OK it highlights "Like" in the sql statement.
For fun, I tried removing the "Like" statements and view but a message
box opens asking me to enter a value for ADM.

Thanks for your assistance.

Try this --
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] & "*";
--
Build a little, test a little.
:
Hi everybody -
I have a problem that I am hoping someone may be able to help me out
with.  I have a form in my database with 5 different combo boxes. I
got some help making a report that is created using VBA that bringsin
data based off of the combo box selections, even if nothing is
selected.  What I am hoping to create is a select query that works in
the same way, so basically I can have two buttons on my form; one
button to create a report and another button that will open the select
query allowing the user to copy the info and paste it into Excel.
I have tried a few times to make a query but none of them have worked
well.  The closest that I came to success had around 36 lines of
criteria (one for every combo box selection possibility) and it was
around 4 pages long in SQL view.
I am hoping someone can help me to get the query working, or if there
is an easier way to get to my goal then I can create whatever is
necessary.
Any help that I can get would be greatly appreciated.
Just in case, my table name is [ARCHIVE_ADM_Summary]
My combo boxes are
[Forms]![Main_Form]![ADM Select]
[Forms]![Main_Form]![Region Select]
[Forms]![Main_Form]![SBT Account Select]
[Forms]![Main_Form]![Month Select]
[Forms]![Main_Form]![Year Select]- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[KARL DEWEY]

I do not see a problem but I also do not see where it would prompt for ADM.
--
Build a little, test a little.

No problem.
Here it is:

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] & "*";

Apparently something messed up in the copying, posting, copying, and then
pasting.
Copy what you wound up with in your query and post back so I can look at it.

--
Build a little, test a little.

Hi Karl -
Thanks for your reply. I tried to use the sql that you provided but I
received a "Syntax error (missing operator) in query expression..."
error. When I press OK it highlights "Like" in the sql statement.
For fun, I tried removing the "Like" statements and view but a message
box opens asking me to enter a value for ADM.

Thanks for your assistance.

On Sep 25, 10:56 am, KARL DEWEY <[email protected]>
wrote:
Try this --
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] & "*";

:
Hi everybody -
I have a problem that I am hoping someone may be able to help me out
with. I have a form in my database with 5 different combo boxes. I
got some help making a report that is created using VBA that brings in
data based off of the combo box selections, even if nothing is
selected. What I am hoping to create is a select query that works in
the same way, so basically I can have two buttons on my form; one
button to create a report and another button that will open the select
query allowing the user to copy the info and paste it into Excel.
I have tried a few times to make a query but none of them have worked
well. The closest that I came to success had around 36 lines of
criteria (one for every combo box selection possibility) and it was
around 4 pages long in SQL view.
I am hoping someone can help me to get the query working, or if there
is an easier way to get to my goal then I can create whatever is
necessary.
Any help that I can get would be greatly appreciated.

Just in case, my table name is [ARCHIVE_ADM_Summary]
My combo boxes are
[Forms]![Main_Form]![ADM Select]
[Forms]![Main_Form]![Region Select]
[Forms]![Main_Form]![SBT Account Select]
[Forms]![Main_Form]![Month Select]
[Forms]![Main_Form]![Year Select]- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[John Spencer]

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]

My mistake from the get go.
--
Build a little, test a little.

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

I do not see a problem but I also do not see where it would prompt for ADM.

 

[Erick C]

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...

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

I do not see a problem but I also do not see where it would prompt for ADM.- Hide quoted text -

- Show quoted text -

 

[John Spencer]

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

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...

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

I do not see a problem but I also do not see where it would prompt for ADM.- Hide quoted text -

- Show quoted text -

 

[Erick C]

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));

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?  IFyou
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 thatyou
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

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...

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 -

 

[KARL DEWEY]

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.

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));

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

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...

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 -

 

[Erick C]

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....

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.

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 -

 

[KARL DEWEY]

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.

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....

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.

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 -

 

[Erick C]

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.

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 coulduse
LIKE with them?

Post sample data as to what is in [Month Select] and [Year Select].

--
Build a little, test a little.

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....

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.
:
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 -

 

[KARL DEWEY]

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.

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.

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.

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 <[email protected]>
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.

:
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 -

 

[Erick C]

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.

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.

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.

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.
:
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 <[email protected]>
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.
:
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 isgoing
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 notknow 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]![RegionSelect])
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]![SBTAccount
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]![SBTAccount
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 -

 

[KARL DEWEY]

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.

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.

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.

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 <[email protected]>
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].

:
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 <[email protected]>
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.

:
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 -

 

[Erick C]

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!

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.

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.

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.
:
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 <[email protected]>
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.
:
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 <[email protected]>
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.
:
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 allrecords,
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 didnot 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.[SBTAccount])=
[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]![YearSelect]) 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]![RegionSelect]) 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]![RegionSelect]) 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]![RegionSelect]) 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]![YearSelect]) 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 -