Two "(ALL)" Cbo box problem

[Pat Coleman]

I have a form with two Cbo boxes that have a list of items and has the
'(All)' choice in each Cbo Box.

I have a query with the criteria set as the result for each Choice for each
Cbo Box. ie [Forms]![Form]![Cbo1]

The query works fine when I pick an actual value from either of the Cbo
Boxes.

But if any of the Values I choose is "(ALL)" nothing is returned in my
query.

I had it working fine also when I had only one of the Cbo's with the "(ALL)"
value but once I added the second, it seems the query was not able to handle
it.

the Query Syntax is

Select ....

WHERE (((VENDOR.NAME)=[Forms]![VendorPriceForm]![CboVendorName]) AND
((CUSTOMFIELDVIEW.INFO)=[Forms]![VendorPriceForm]![CboPartCategory])) OR
((([Forms]![VendorPriceForm]![CboVendorName])="(ALL)") AND
(([Forms]![VendorPriceForm]![CboPartCategory])="(ALL)"));

 

[Douglas J. Steele]

Change your condition from

[Forms]![Form]![Cbo1]

to

[Forms]![Form]![Cbo1] Or [Forms]![Form]![Cbo1] = "(ALL)"

 

[Pat Coleman]

I can not seem to crack this one.

The query is based on a set of values based on the form with two combo
boxes.

[CboVendorName] and [CboPartCategory]

In the Form, each combo box has "(ALL)" as an option in each list.

The SQL from the query is as follows

Select .......
From ......

WHERE ( ((VENDOR.NAME)=[Forms]![VendorPriceForm]![CboVendorName]) OR
(([Forms]![VendorPriceForm]![CboVendorName])="(ALL)") )

AND

( ((CUSTOMFIELDVIEW.INFO)=[Forms]![VendorPriceForm]![CboPartCategory])
OR (([Forms]![VendorPriceForm]![CboPartCategory])="(ALL)") );


The Query results as follows

Senario 1
[CboVendorName] = VENDOR.NAME
[CboPartCategory] = "(ALL)"

Above does not work - returns all values from query as if no filter was
applied

Senario 2
[CboVendorName] = VENDOR.NAME
[CboPartCategory] = "(ALL)"

Above Works correctly - filters as expected.



Senario 3
[CboVendorName] = "(ALL)"
[CboPartCategory] = CUSTOMFIELDVIEW.INFO

Above Works correctly - filters as expected.

Senario 4

[CboVendorName] = "(ALL)"
[CboPartCategory] = "(ALL)"

Above Works correctly - filters as expected.

Change your condition from

[Forms]![Form]![Cbo1]

to

[Forms]![Form]![Cbo1] Or [Forms]![Form]![Cbo1] = "(ALL)"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a form with two Cbo boxes that have a list of items and has the
'(All)' choice in each Cbo Box.

I have a query with the criteria set as the result for each Choice for
each Cbo Box. ie [Forms]![Form]![Cbo1]

The query works fine when I pick an actual value from either of the Cbo
Boxes.

But if any of the Values I choose is "(ALL)" nothing is returned in my
query.

I had it working fine also when I had only one of the Cbo's with the
"(ALL)" value but once I added the second, it seems the query was not
able to handle it.

the Query Syntax is

Select ....

WHERE (((VENDOR.NAME)=[Forms]![VendorPriceForm]![CboVendorName]) AND
((CUSTOMFIELDVIEW.INFO)=[Forms]![VendorPriceForm]![CboPartCategory])) OR
((([Forms]![VendorPriceForm]![CboVendorName])="(ALL)") AND
(([Forms]![VendorPriceForm]![CboPartCategory])="(ALL)"));

 

[John W. Vinson]

I can not seem to crack this one.

The query is based on a set of values based on the form with two combo
boxes.

What are the Rowsource and Bound Column properties of the combos? Might the
bound column be a (hidden) number field rather than the text value you're
testing for?

 

[Pat Coleman]

Row Source [CboVendorName]
SELECT VendorName FROM VendorNameTable UNION Select "(All)" as NAME FROM
VendorNameTable;
Bound Column = 1


Row Source [CboPartCategory]
SELECT PartCategory FROM PartCategoryTable UNION Select "(All)" as NAME FROM
PartCategoryTable;
Bound Column = 1


Thanks

Pat

 

[John W. Vinson]

The query works fine when I pick an actual value from either of the Cbo
Boxes.

But if any of the Values I choose is "(ALL)" nothing is returned in my
query.

I had it working fine also when I had only one of the Cbo's with the "(ALL)"
value but once I added the second, it seems the query was not able to handle
it.

I think the OR logic is wrong in your query. Try

WHERE (VENDOR.NAME=[Forms]![VendorPriceForm]![CboVendorName] OR
[Forms]![VendorPriceForm]![CboVendorName])="(ALL)")
AND
(CUSTOMFIELDVIEW.INFO=[Forms]![VendorPriceForm]![CboPartCategory] OR
[Forms]![VendorPriceForm]![CboPartCategory]="(ALL)");

 

[Pat Coleman]

Bingo!

Too Many OR's and not enough AND's

The query works fine when I pick an actual value from either of the Cbo
Boxes.

But if any of the Values I choose is "(ALL)" nothing is returned in my
query.

I had it working fine also when I had only one of the Cbo's with the
"(ALL)"
value but once I added the second, it seems the query was not able to
handle
it.

I think the OR logic is wrong in your query. Try

WHERE (VENDOR.NAME=[Forms]![VendorPriceForm]![CboVendorName] OR
[Forms]![VendorPriceForm]![CboVendorName])="(ALL)")
AND
(CUSTOMFIELDVIEW.INFO=[Forms]![VendorPriceForm]![CboPartCategory] OR
[Forms]![VendorPriceForm]![CboPartCategory]="(ALL)");