Using Combo Boxes as filter criteria

G

Guest

Hi,

I have three combo boxes that I would like to use as search criteria for a
form and sub form.

The three combo boxes are on the maniform, and when their values change, I
would like the sub form filter certain fields for the values in the combo
boxes.

Each one of my combo boxes reads a simple table (for example, combo box # 1
reads the frequency table which list the values daily, weekly, monthly,
quarterly, yearly). This table is used elsewhere in my database.

What I would like to do, is dynamically add the value/word "ALL" to my combo
box (which effectively turns the filter off for this combo box off), so that
the following is listed when the combo box is opened:

ALL
Daily
Weekly
Monthly
etc etc

I can't add the 'ALL' to the original table, because it is used in other
parts of the database and would not make sense to the user.

Any help would be greatly appreciated.

Cheers,

GLT
 
G

Guest

Set the default of the combo to "ALL" and that is what you will have if you
do not select anything.
One drawback is that if you select and then change your min you can only get
"ALL" by closing and re-opening the form.
 
B

Bob Quintal

Hi,

I have three combo boxes that I would like to use as search
criteria for a form and sub form.

The three combo boxes are on the maniform, and when their values
change, I would like the sub form filter certain fields for the
values in the combo boxes.

Each one of my combo boxes reads a simple table (for example,
combo box # 1 reads the frequency table which list the values
daily, weekly, monthly, quarterly, yearly). This table is used
elsewhere in my database.

What I would like to do, is dynamically add the value/word "ALL"
to my combo box (which effectively turns the filter off for this
combo box off), so that the following is listed when the combo box
is opened:

ALL
Daily
Weekly
Monthly
etc etc

I can't add the 'ALL' to the original table, because it is used in
other parts of the database and would not make sense to the user.

Any help would be greatly appreciated.

Cheers,

GLT
Click to expand...

The technique is to use a union query to add <<all>> to the list.

Let's say you currently have "SELECT frequency FROM tblFrequencies;"
as the query in the row source of the combobox
change to
"SELECT "<<ALL>" as frequency FROM tblFrequencies UNION SELECT
frequency FROM tblFrequencies;"

Then you need some code to not filter the record if all is selected.
Need more info to help here.
 
G

Guest

Hi Bob,

Thanks for your reply - I wound up putting this code in the row/souce column:

SELECT "<ALL>" as tbl_ALL_Freq.[Type L] FROM tbl_ALL_Freq UNION SELECT
tbl_ALL_Freq.[Type L] FROM tbl_ALL_Freq;

When I try to select something it says there is the SELECT statement is
using a reserved word or argument name that is mispelled or missing. I have
checked and double checked spelling of the table and field names and they are
both correct...

Might this have something to do with the quotes?
 
B

Bob Quintal

Hi Bob,

Thanks for your reply - I wound up putting this code in the
row/souce column:

SELECT "<ALL>" as tbl_ALL_Freq.[Type L] FROM tbl_ALL_Freq UNION
SELECT tbl_ALL_Freq.[Type L] FROM tbl_ALL_Freq;

When I try to select something it says there is the SELECT
statement is using a reserved word or argument name that is
mispelled or missing. I have checked and double checked spelling
of the table and field names and they are both correct...

Might this have something to do with the quotes?
Click to expand...

No not the quotes, delete the table reference in the assigned field
Bob Quintal said:
The technique is to use a union query to add <<all>> to the list.

Let's say you currently have "SELECT frequency FROM
tblFrequencies;" as the query in the row source of the combobox
change to
"SELECT "<<ALL>" as frequency FROM tblFrequencies UNION SELECT
frequency FROM tblFrequencies;"

Then you need some code to not filter the record if all is
selected. Need more info to help here.
Click to expand...
Click to expand...
 
G

Guest

Hi Bob,

Thanks that worked fine - thanks so much for your help here.

One last thing - I have created a query where the values of the three combo
boxes are entered as search criteria for the three feilds - this works fine.

Now it needs to detect the "<ALL>" and substitutue a "*" so I think it would
be something like this:

iif(([forms]![frm_TAPMAN_RecDetails]![cbo_Site] )= "<ALL>" then
(([forms]![frm_TAPMAN_RecDetails]![cbo_Site] )= "'*'"

Excuse my lack of SQL knowledge here but everytime I work with SQL and
strings it drives me nuts...

Cheers,


Bob Quintal said:
Hi Bob,

Thanks for your reply - I wound up putting this code in the
row/souce column:

SELECT "<ALL>" as tbl_ALL_Freq.[Type L] FROM tbl_ALL_Freq UNION
SELECT tbl_ALL_Freq.[Type L] FROM tbl_ALL_Freq;

When I try to select something it says there is the SELECT
statement is using a reserved word or argument name that is
mispelled or missing. I have checked and double checked spelling
of the table and field names and they are both correct...

Might this have something to do with the quotes?
Click to expand...

No not the quotes, delete the table reference in the assigned field
Click to expand...
 
G

Guest

The last line in the AfterUpdate of the ComboBox:
Me![ComboBox] = "All"

Puts the "All" value back without closing the from.
 
B

Bob Quintal

Hi Bob,

Thanks that worked fine - thanks so much for your help here.

One last thing - I have created a query where the values of the
three combo boxes are entered as search criteria for the three
feilds - this works fine.

Now it needs to detect the "<ALL>" and substitutue a "*" so I
think it would be something like this:

iif(([forms]![frm_TAPMAN_RecDetails]![cbo_Site] )= "<ALL>" then
(([forms]![frm_TAPMAN_RecDetails]![cbo_Site] )= "'*'"

Excuse my lack of SQL knowledge here but everytime I work with SQL
and strings it drives me nuts...

Cheers,
Click to expand...
SQL is a very simple language and rich in its abilities. you don't
need the iif statement, just add a calculated expression to the
query and put "<ALL>" as its criteria. Uncheck the Show: checkbox

[forms]![frm_TAPMAN_RecDetails]![cbo_Site]

With two combos with all you will need four criteria rows
Edit bla to the naew ofyour second combobox and paste this into the
query in SQL view to see how it looks in design view.

WHERE (((TAPMAN.site)=[forms]![frm_TAPMAN_RecDetails]![cbo_Site])
AND ((TAPMAN.bla)=[forms]![frm_TAPMAN_RecDetails]![bla])) OR
(((TAPMAN.site)=[forms]![frm_TAPMAN_RecDetails]![cbo_Site]) AND
(([forms]![frm_TAPMAN_RecDetails]![bla])="<All>")) OR ((([forms]!
[frm_TAPMAN_RecDetails]![bla])="<All>") AND (([forms]!
[frm_TAPMAN_RecDetails]![cbo_Site])="<All>")) OR (((TAPMAN.bla)=
[forms]![frm_TAPMAN_RecDetails]![bla]) AND (([forms]!
[frm_TAPMAN_RecDetails]![cbo_Site])="<All>"));
 
G

Guest

Hi Bob,

Will the following work with four combo boxes?

I tried this the following:

SELECT tbl_TAPMAN_StkTapDet.ID, tbl_TAPMAN_StkTapDet.Site,
tbl_TAPMAN_StkTapDet.Use, tbl_TAPMAN_StkTapDet.Manuf,
tbl_TAPMAN_StkTapDet.[Sub Type], tbl_TAPMAN_StkTapDet.Density,
tbl_TAPMAN_StkTapDet.Qty, tbl_TAPMAN_StkTapDet.Min, tbl_TAPMAN_StkTapDet.Req,
tbl_TAPMAN_StkTapDet.UseFor, tbl_TAPMAN_StkTapDet.Cab,
tbl_TAPMAN_StkTapDet.Row, tbl_TAPMAN_StkTapDet.Sec, tbl_TAPMAN_StkTapDet.Bar
FROM tbl_TAPMAN_StkTapDet
WHERE (((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site) And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site) And
((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>")) Or
(((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>") And
((forms!frm_TAPMAN_StkTapDet!cmb_Site)="<All>")) Or
(((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((forms!frm_TAPMAN_StkTapDet!cmb_Site)="<All>") And
((forms!frm_TAPMAN_StkTapDet!cmb_TapTyp)="<ALL>")) Or
(((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam) And
((forms!frm_TAPMAN_StkTapDet!cmb_TapTyp)="<ALL>") And
((forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)="<ALL>")) Or
(((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp) And
((forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)="<ALL>"));

It works with two combo boxes but not with 4 - Can you advise where I am
going wrong here?

Cheers,
GLT

Bob Quintal said:
Hi Bob,

Thanks that worked fine - thanks so much for your help here.

One last thing - I have created a query where the values of the
three combo boxes are entered as search criteria for the three
feilds - this works fine.

Now it needs to detect the "<ALL>" and substitutue a "*" so I
think it would be something like this:

iif(([forms]![frm_TAPMAN_RecDetails]![cbo_Site] )= "<ALL>" then
(([forms]![frm_TAPMAN_RecDetails]![cbo_Site] )= "'*'"

Excuse my lack of SQL knowledge here but everytime I work with SQL
and strings it drives me nuts...

Cheers,
Click to expand...
SQL is a very simple language and rich in its abilities. you don't
need the iif statement, just add a calculated expression to the
query and put "<ALL>" as its criteria. Uncheck the Show: checkbox

[forms]![frm_TAPMAN_RecDetails]![cbo_Site]

With two combos with all you will need four criteria rows
Edit bla to the naew ofyour second combobox and paste this into the
query in SQL view to see how it looks in design view.

WHERE (((TAPMAN.site)=[forms]![frm_TAPMAN_RecDetails]![cbo_Site])
AND ((TAPMAN.bla)=[forms]![frm_TAPMAN_RecDetails]![bla])) OR
(((TAPMAN.site)=[forms]![frm_TAPMAN_RecDetails]![cbo_Site]) AND
(([forms]![frm_TAPMAN_RecDetails]![bla])="<All>")) OR ((([forms]!
[frm_TAPMAN_RecDetails]![bla])="<All>") AND (([forms]!
[frm_TAPMAN_RecDetails]![cbo_Site])="<All>")) OR (((TAPMAN.bla)=
[forms]![frm_TAPMAN_RecDetails]![bla]) AND (([forms]!
[frm_TAPMAN_RecDetails]![cbo_Site])="<All>"));
Click to expand...
 
R

rquintal

Hi Bob,

Will the following work with four combo boxes?

I tried this the following:

SELECT tbl_TAPMAN_StkTapDet.ID, tbl_TAPMAN_StkTapDet.Site,
tbl_TAPMAN_StkTapDet.Use, tbl_TAPMAN_StkTapDet.Manuf,
tbl_TAPMAN_StkTapDet.[Sub Type], tbl_TAPMAN_StkTapDet.Density,
tbl_TAPMAN_StkTapDet.Qty, tbl_TAPMAN_StkTapDet.Min, tbl_TAPMAN_StkTapDet.Req,
tbl_TAPMAN_StkTapDet.UseFor, tbl_TAPMAN_StkTapDet.Cab,
tbl_TAPMAN_StkTapDet.Row, tbl_TAPMAN_StkTapDet.Sec, tbl_TAPMAN_StkTapDet.Bar
FROM tbl_TAPMAN_StkTapDet
WHERE (((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site) And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site) And
((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>")) Or
(((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>") And
((forms!frm_TAPMAN_StkTapDet!cmb_Site)="<All>")) Or
(((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((forms!frm_TAPMAN_StkTapDet!cmb_Site)="<All>") And
((forms!frm_TAPMAN_StkTapDet!cmb_TapTyp)="<ALL>")) Or
(((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam) And
((forms!frm_TAPMAN_StkTapDet!cmb_TapTyp)="<ALL>") And
((forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)="<ALL>")) Or
(((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp) And
((forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)="<ALL>"));

It works with two combo boxes but not with 4 - Can you advise where I am
going wrong here?

Cheers,
GLT



Bob Quintal said:
Hi Bob,
Thanks that worked fine - thanks so much for your help here.
One last thing - I have created a query where the values of the
three combo boxes are entered as search criteria for the three
feilds - this works fine.
Now it needs to detect the "<ALL>" and substitutue a "*" so I
think it would be something like this:
iif(([forms]![frm_TAPMAN_RecDetails]![cbo_Site] )= "<ALL>" then
(([forms]![frm_TAPMAN_RecDetails]![cbo_Site] )= "'*'"
Excuse my lack of SQL knowledge here but everytime I work with SQL
and strings it drives me nuts...
Cheers,
Click to expand...
Click to expand...
SQL is a very simple language and rich in its abilities. you don't
need the iif statement, just add a calculated expression to the
query and put "<ALL>" as its criteria. Uncheck the Show: checkbox
[forms]![frm_TAPMAN_RecDetails]![cbo_Site]
Click to expand...

With two combos with all you will need four criteria rows
Edit bla to the naew ofyour second combobox and paste this into the
query in SQL view to see how it looks in design view.
Click to expand...
WHERE (((TAPMAN.site)=[forms]![frm_TAPMAN_RecDetails]![cbo_Site])
AND ((TAPMAN.bla)=[forms]![frm_TAPMAN_RecDetails]![bla])) OR
(((TAPMAN.site)=[forms]![frm_TAPMAN_RecDetails]![cbo_Site]) AND
(([forms]![frm_TAPMAN_RecDetails]![bla])="<All>")) OR ((([forms]!
[frm_TAPMAN_RecDetails]![bla])="<All>") AND (([forms]!
[frm_TAPMAN_RecDetails]![cbo_Site])="<All>")) OR (((TAPMAN.bla)=
[forms]![frm_TAPMAN_RecDetails]![bla]) AND (([forms]!
[frm_TAPMAN_RecDetails]![cbo_Site])="<All>"));
Click to expand...
PA is y I've altered my email address.
Click to expand...

- Show quoted text -
Click to expand...

going from 2 comboboxes to 4 comboboxes means going from 4 tests to 16
tests. You are missing a bunch in the Where Clause

I'll generate them and post later, I don't have time right now.
 
G

Guest

Ok thanks for your help - one of my forms has three combos and the other has
four - Im going to try to figure this out with the three combos (I assume
there is 12 tests with three combos) ....

Cheers
GLT

[email protected] said:
Hi Bob,

Will the following work with four combo boxes?

I tried this the following:

SELECT tbl_TAPMAN_StkTapDet.ID, tbl_TAPMAN_StkTapDet.Site,
tbl_TAPMAN_StkTapDet.Use, tbl_TAPMAN_StkTapDet.Manuf,
tbl_TAPMAN_StkTapDet.[Sub Type], tbl_TAPMAN_StkTapDet.Density,
tbl_TAPMAN_StkTapDet.Qty, tbl_TAPMAN_StkTapDet.Min, tbl_TAPMAN_StkTapDet.Req,
tbl_TAPMAN_StkTapDet.UseFor, tbl_TAPMAN_StkTapDet.Cab,
tbl_TAPMAN_StkTapDet.Row, tbl_TAPMAN_StkTapDet.Sec, tbl_TAPMAN_StkTapDet.Bar
FROM tbl_TAPMAN_StkTapDet
WHERE (((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site) And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site) And
((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>")) Or
(((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>") And
((forms!frm_TAPMAN_StkTapDet!cmb_Site)="<All>")) Or
(((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((forms!frm_TAPMAN_StkTapDet!cmb_Site)="<All>") And
((forms!frm_TAPMAN_StkTapDet!cmb_TapTyp)="<ALL>")) Or
(((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam) And
((forms!frm_TAPMAN_StkTapDet!cmb_TapTyp)="<ALL>") And
((forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)="<ALL>")) Or
(((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp) And
((forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)="<ALL>"));

It works with two combo boxes but not with 4 - Can you advise where I am
going wrong here?

Cheers,
GLT



Bob Quintal said:
news:[email protected]:
Click to expand...
Hi Bob,
Click to expand...
Thanks that worked fine - thanks so much for your help here.
Click to expand...
One last thing - I have created a query where the values of the
three combo boxes are entered as search criteria for the three
feilds - this works fine.
Click to expand...
Now it needs to detect the "<ALL>" and substitutue a "*" so I
think it would be something like this:
Click to expand...
iif(([forms]![frm_TAPMAN_RecDetails]![cbo_Site] )= "<ALL>" then
(([forms]![frm_TAPMAN_RecDetails]![cbo_Site] )= "'*'"
Click to expand...
Excuse my lack of SQL knowledge here but everytime I work with SQL
and strings it drives me nuts...
Cheers,
Click to expand...

SQL is a very simple language and rich in its abilities. you don't
need the iif statement, just add a calculated expression to the
query and put "<ALL>" as its criteria. Uncheck the Show: checkbox
[forms]![frm_TAPMAN_RecDetails]![cbo_Site]
Click to expand...

With two combos with all you will need four criteria rows
Edit bla to the naew ofyour second combobox and paste this into the
query in SQL view to see how it looks in design view.
Click to expand...
WHERE (((TAPMAN.site)=[forms]![frm_TAPMAN_RecDetails]![cbo_Site])
AND ((TAPMAN.bla)=[forms]![frm_TAPMAN_RecDetails]![bla])) OR
(((TAPMAN.site)=[forms]![frm_TAPMAN_RecDetails]![cbo_Site]) AND
(([forms]![frm_TAPMAN_RecDetails]![bla])="<All>")) OR ((([forms]!
[frm_TAPMAN_RecDetails]![bla])="<All>") AND (([forms]!
[frm_TAPMAN_RecDetails]![cbo_Site])="<All>")) OR (((TAPMAN.bla)=
[forms]![frm_TAPMAN_RecDetails]![bla]) AND (([forms]!
[frm_TAPMAN_RecDetails]![cbo_Site])="<All>"));
Click to expand...
PA is y I've altered my email address.
Click to expand...

- Show quoted text -
Click to expand...

going from 2 comboboxes to 4 comboboxes means going from 4 tests to 16
tests. You are missing a bunch in the Where Clause

I'll generate them and post later, I don't have time right now.
Click to expand...
 
B

Bob Quintal

Ok thanks for your help - one of my forms has three combos and the
other has four - Im going to try to figure this out with the three
combos (I assume there is 12 tests with three combos) ....

Cheers
GLT
Click to expand...

Nope, there are 9 groups with 3 fields and 16 with 4, but the three
fields has three fields in each of the nine combinations, the four
box set has 16 combinations of four fields.

Here is the where clause for the four box combination: please don't
ask for five comboboxes.

WHERE
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)= forms!frm_TAPMAN_StkTapDet!
cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)= "<All>" ) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)= "<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>" And
((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>")) And
(((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>") And
((forms!frm_TAPMAN_StkTapDet!cmb_Site)="<All>"))
 
G

Guest

Hi Bob,

Wow Im guessing that maybe four combo boxes is not the most efficient way of
doing this?

Is there some better way of doing this?

I made a few name changes (to make the DB easier to read) and used the
following SQL:

WHERE
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)= forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)= "<All>" ) And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)= "<All>") And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)="<All>" And
((forms![frm_TM_STK: TapDet]!cmb_Use)="<All>")) And
(((forms![frm_TM_STK: TapDet]!cmb_Use)="<All>") And
((forms![frm_TM_STK: TapDet]!cmb_Site)="<All>")));

However this does not return any results when I test it out - is there some
way of debugging the SQL?

Cheers,
GLT
 
B

Bob Quintal

This is my attempt at the three combo box:

SELECT [tbl_TM_REC: Details].ID, [tbl_TM_REC: Details].BoxID,
[tbl_TM_REC: Details].BoxNo, [tbl_TM_REC: Details].Site,
[tbl_TM_REC: Details].Retention, [tbl_TM_REC: Details].[Box
Status], [tbl_TM_REC: Details].[Quantum No], [tbl_TM_REC:
Details].[Recalled By], [tbl_TM_REC: Details].[Recall Date],
[tbl_TM_REC: Details].Urgency, [tbl_TM_REC: Details].Notify,
[tbl_TM_REC: Details].Special, [tbl_TM_REC: Details].Close,
[tbl_TM_REC: Details].[Close Date]
FROM [tbl_TM_REC: Details]
WHERE
((([tbl_TM_REC: Details].Site)=[forms]![frm_TM_REC:
Details]![cmb_Site]) AND
(([tbl_TM_REC: Details].Retention)=[forms]![frm_TM_REC:
Details]![cmb_Freq]) AND
(([tbl_TM_REC: Details].[Box Status])=[forms]![frm_TM_REC:
Details]![cmb_Stat]))
OR
((([tbl_TM_REC: Details].Site)="<ALL>") AND
(([tbl_TM_REC: Details].Retention)=[forms]![frm_TM_REC:
Details]![cmb_Freq]) AND
(([tbl_TM_REC: Details].[Box Status])=[forms]![frm_TM_REC:
Details]![cmb_Stat]))
OR
((([tbl_TM_REC: Details].Site)=[forms]![frm_TM_REC:
Details]![cmb_Site]) AND
(([tbl_TM_REC: Details].Retention)="<ALL>") AND
(([tbl_TM_REC: Details].[Box Status])=[forms]![frm_TM_REC:
Details]![cmb_Stat]) AND
(([forms]![frm_TM_REC: Details]![cmb_Freq])="<ALL>"))
OR
((([tbl_TM_REC: Details].Site)=[forms]![frm_TM_REC:
Details]![cmb_Site]) AND
(([tbl_TM_REC: Details].Retention)=[forms]![frm_TM_REC:
Details]![cmb_Freq]) AND
(([tbl_TM_REC: Details].[Box Status])="<ALL>") AND
(([forms]![frm_TM_REC:
Details]![cmb_Stat])="<ALL>"))
OR
((([tbl_TM_REC: Details].Site)="<ALL>") AND
(([tbl_TM_REC: Details].Retention)="<ALL>") AND
(([tbl_TM_REC: Details].[Box Status])="<ALL>"))
OR
((([forms]![frm_TM_REC: Details]![cmb_Site])="<ALL>"));

When I put this into the QBE grid - i made all possible
combinations (ie. 3 x 3 = 9), but I dont undertstand at the end of
the qbe grid, there are extra columns with "<ALL>" in them - is
this where I am going wrong?
Click to expand...
That's acess crazy way of representing the filters. You'll see
criteria under the columns. eiter true or blank.

but it should work. See my other message for a better way.


Q
GLT said:
Hi Bob,

Wow Im guessing that maybe four combo boxes is not the most
efficient way of doing this?

Is there some better way of doing this?

I made a few name changes (to make the DB easier to read) and
used the following SQL:

WHERE
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK:
TapDet]!cmb_Site) And (([tbl_TM_STK:
TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK:
TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type)) Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use)
And (([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK:
TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type)) Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK:
TapDet]!cmb_Site) And (([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK:
TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type)) Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK:
TapDet]!cmb_Site) And (([tbl_TM_STK:
TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And (([tbl_TM_STK:
TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type)) Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK:
TapDet]!cmb_Site) And (([tbl_TM_STK:
TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK: TapDet].Type)=
forms![frm_TM_STK: TapDet]!cmb_Type)) Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use)
And (([tbl_TM_STK: TapDet].Manuf)= "<All>" ) And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK:
TapDet]!cmb_Type)) Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use)
And (([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK:
TapDet]!cmb_Site) And (([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)= "<All>") And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK:
TapDet]!cmb_Type)) Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK:
TapDet]!cmb_Site) And (([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK:
TapDet]!cmb_Site) And (([tbl_TM_STK:
TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And (([tbl_TM_STK:
TapDet].Type)= "<All>")) Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK:
TapDet]!cmb_Type)) Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use)
And (([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK:
TapDet]!cmb_Site) And (([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)="<All>" And
((forms![frm_TM_STK: TapDet]!cmb_Use)="<All>")) And
(((forms![frm_TM_STK: TapDet]!cmb_Use)="<All>") And
((forms![frm_TM_STK: TapDet]!cmb_Site)="<All>")));

However this does not return any results when I test it out - is
there some way of debugging the SQL?

Cheers,
GLT

Bob Quintal said:
Ok thanks for your help - one of my forms has three combos
and the other has four - Im going to try to figure this out
with the three combos (I assume there is 12 tests with three
combos) ....

Cheers
GLT

Nope, there are 9 groups with 3 fields and 16 with 4, but the
three fields has three fields in each of the nine combinations,
the four box set has 16 combinations of four fields.

Here is the where clause for the four box combination: please
don't ask for five comboboxes.

WHERE
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Sit
e) And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_Brd
Nam) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapT
yp)) Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_Brd
Nam) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapT
yp)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Sit
e) And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_Brd
Nam) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapT
yp)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Sit
e) And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And ((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapT
yp)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Sit
e) And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_Brd
Nam) And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_Brd
Nam) And
((tbl_TAPMAN_StkTapDet.Type)= forms!frm_TAPMAN_StkTapDet!
cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And ((tbl_TAPMAN_StkTapDet.Manuf)= "<All>" ) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapT
yp)) Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_Brd
Nam) And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Sit
e) And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)= "<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapT
yp)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Sit
e) And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_Brd
Nam) And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Sit
e) And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And ((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapT
yp)) Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_Brd
Nam) And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And ((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Sit
e) And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>" And
((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>")) And
(((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>") And
((forms!frm_TAPMAN_StkTapDet!cmb_Site)="<All>"))
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Quintal

Hi Bob,

Wow Im guessing that maybe four combo boxes is not the most
efficient way of doing this?

Is there some better way of doing this?
Click to expand...

There is always a better way to do everything (thay's my personal
philosophy) If you are using the query in a form or report, you can
instead of filtering at the query level create the appropriate
filter and use the Whereclause parameter of the openform method to
pass that filter to the query. If you are using the query in an
exportTo, generate the whole query in code.

Where are you using the query?


Q
I made a few name changes (to make the DB easier to read) and used
the following SQL:

WHERE
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK:
TapDet]!cmb_Use) And (([tbl_TM_STK:
TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use)
And (([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK:
TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type)) Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK:
TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type)) Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK:
TapDet]!cmb_Use) And (([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK:
TapDet]!cmb_Use) And (([tbl_TM_STK:
TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)= "<All>")) Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK: TapDet].Type)=
forms![frm_TM_STK: TapDet]!cmb_Type)) Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use)
And (([tbl_TM_STK: TapDet].Manuf)= "<All>" ) And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use)
And (([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)= "<All>") And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK:
TapDet]!cmb_Use) And (([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use)
And (([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)="<All>" And
((forms![frm_TM_STK: TapDet]!cmb_Use)="<All>")) And
(((forms![frm_TM_STK: TapDet]!cmb_Use)="<All>") And
((forms![frm_TM_STK: TapDet]!cmb_Site)="<All>")));

However this does not return any results when I test it out - is
there some way of debugging the SQL?

Cheers,
GLT

Bob Quintal said:
Nope, there are 9 groups with 3 fields and 16 with 4, but the
three fields has three fields in each of the nine combinations,
the four box set has 16 combinations of four fields.

Here is the where clause for the four box combination: please
don't ask for five comboboxes.

WHERE
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapTyp
)) Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapTyp
)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapTyp
)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And ((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapTyp
)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)= forms!frm_TAPMAN_StkTapDet!
cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And ((tbl_TAPMAN_StkTapDet.Manuf)= "<All>" ) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapTyp
)) Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)= "<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapTyp
)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And ((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapTyp
)) Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And ((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>" And
((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>")) And
(((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>") And
((forms!frm_TAPMAN_StkTapDet!cmb_Site)="<All>"))
Click to expand...
Click to expand...
 
G

Guest

This is my attempt at the three combo box:

SELECT [tbl_TM_REC: Details].ID, [tbl_TM_REC: Details].BoxID, [tbl_TM_REC:
Details].BoxNo, [tbl_TM_REC: Details].Site, [tbl_TM_REC: Details].Retention,
[tbl_TM_REC: Details].[Box Status], [tbl_TM_REC: Details].[Quantum No],
[tbl_TM_REC: Details].[Recalled By], [tbl_TM_REC: Details].[Recall Date],
[tbl_TM_REC: Details].Urgency, [tbl_TM_REC: Details].Notify, [tbl_TM_REC:
Details].Special, [tbl_TM_REC: Details].Close, [tbl_TM_REC: Details].[Close
Date]
FROM [tbl_TM_REC: Details]
WHERE
((([tbl_TM_REC: Details].Site)=[forms]![frm_TM_REC: Details]![cmb_Site]) AND
(([tbl_TM_REC: Details].Retention)=[forms]![frm_TM_REC:
Details]![cmb_Freq]) AND
(([tbl_TM_REC: Details].[Box Status])=[forms]![frm_TM_REC:
Details]![cmb_Stat]))
OR
((([tbl_TM_REC: Details].Site)="<ALL>") AND
(([tbl_TM_REC: Details].Retention)=[forms]![frm_TM_REC:
Details]![cmb_Freq]) AND
(([tbl_TM_REC: Details].[Box Status])=[forms]![frm_TM_REC:
Details]![cmb_Stat]))
OR
((([tbl_TM_REC: Details].Site)=[forms]![frm_TM_REC: Details]![cmb_Site]) AND
(([tbl_TM_REC: Details].Retention)="<ALL>") AND
(([tbl_TM_REC: Details].[Box Status])=[forms]![frm_TM_REC:
Details]![cmb_Stat]) AND
(([forms]![frm_TM_REC: Details]![cmb_Freq])="<ALL>"))
OR
((([tbl_TM_REC: Details].Site)=[forms]![frm_TM_REC: Details]![cmb_Site]) AND
(([tbl_TM_REC: Details].Retention)=[forms]![frm_TM_REC: Details]![cmb_Freq])
AND
(([tbl_TM_REC: Details].[Box Status])="<ALL>") AND (([forms]![frm_TM_REC:
Details]![cmb_Stat])="<ALL>"))
OR
((([tbl_TM_REC: Details].Site)="<ALL>") AND
(([tbl_TM_REC: Details].Retention)="<ALL>") AND
(([tbl_TM_REC: Details].[Box Status])="<ALL>"))
OR
((([forms]![frm_TM_REC: Details]![cmb_Site])="<ALL>"));

When I put this into the QBE grid - i made all possible combinations (ie. 3
x 3 = 9), but I dont undertstand at the end of the qbe grid, there are extra
columns with "<ALL>" in them - is this where I am going wrong?

GLT said:
Hi Bob,

Wow Im guessing that maybe four combo boxes is not the most efficient way of
doing this?

Is there some better way of doing this?

I made a few name changes (to make the DB easier to read) and used the
following SQL:

WHERE
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)= forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)= "<All>" ) And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)= "<All>") And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use) And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site) And
(([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)="<All>" And
((forms![frm_TM_STK: TapDet]!cmb_Use)="<All>")) And
(((forms![frm_TM_STK: TapDet]!cmb_Use)="<All>") And
((forms![frm_TM_STK: TapDet]!cmb_Site)="<All>")));

However this does not return any results when I test it out - is there some
way of debugging the SQL?

Cheers,
GLT

Bob Quintal said:
Nope, there are 9 groups with 3 fields and 16 with 4, but the three
fields has three fields in each of the nine combinations, the four
box set has 16 combinations of four fields.

Here is the where clause for the four box combination: please don't
ask for five comboboxes.

WHERE
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)= forms!frm_TAPMAN_StkTapDet!
cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)= "<All>" ) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)= "<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet!cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet!cmb_BrdNam)
And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use) And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet!cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>" And
((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>")) And
(((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>") And
((forms!frm_TAPMAN_StkTapDet!cmb_Site)="<All>"))
Click to expand...
Click to expand...
 
G

Guest

Hi Bob,

This is just a query atached to a form (for the 3 and 4 combo box).

The combo box values are entered into the QBE grid of the query (so if you
run the query by itself (without the form) it will prompt me for each one of
the combo box values.

Do you have any examples that you can point me to regarding Whereclause
parameter?

Cheers,
GLT

Bob Quintal said:
Hi Bob,

Wow Im guessing that maybe four combo boxes is not the most
efficient way of doing this?

Is there some better way of doing this?
Click to expand...

There is always a better way to do everything (thay's my personal
philosophy) If you are using the query in a form or report, you can
instead of filtering at the query level create the appropriate
filter and use the Whereclause parameter of the openform method to
pass that filter to the query. If you are using the query in an
exportTo, generate the whole query in code.

Where are you using the query?


Q
I made a few name changes (to make the DB easier to read) and used
the following SQL:

WHERE
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK:
TapDet]!cmb_Use) And (([tbl_TM_STK:
TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use)
And (([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK:
TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type)) Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK:
TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type)) Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK:
TapDet]!cmb_Use) And (([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK:
TapDet]!cmb_Use) And (([tbl_TM_STK:
TapDet].Manuf)=forms![frm_TM_STK: TapDet]!cmb_Manuf) And
(([tbl_TM_STK: TapDet].Type)= "<All>")) Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK: TapDet].Type)=
forms![frm_TM_STK: TapDet]!cmb_Type)) Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use)
And (([tbl_TM_STK: TapDet].Manuf)= "<All>" ) And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)="<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use)
And (([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)= "<All>") And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK:
TapDet]!cmb_Use) And (([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)="<All>") And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)=forms![frm_TM_STK: TapDet]!cmb_Type))
Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)=forms![frm_TM_STK:
TapDet]!cmb_Manuf) And (([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)= "<All>") And
(([tbl_TM_STK: TapDet].Use)=forms![frm_TM_STK: TapDet]!cmb_Use)
And (([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)= "<All>"))
Or
((([tbl_TM_STK: TapDet].Site)=forms![frm_TM_STK: TapDet]!cmb_Site)
And (([tbl_TM_STK: TapDet].Use)= "<All>") And
(([tbl_TM_STK: TapDet].Manuf)="<All>") And
(([tbl_TM_STK: TapDet].Type)="<All>"))
Or
((([tbl_TM_STK: TapDet].Site)="<All>" And
((forms![frm_TM_STK: TapDet]!cmb_Use)="<All>")) And
(((forms![frm_TM_STK: TapDet]!cmb_Use)="<All>") And
((forms![frm_TM_STK: TapDet]!cmb_Site)="<All>")));

However this does not return any results when I test it out - is
there some way of debugging the SQL?

Cheers,
GLT

Bob Quintal said:
Ok thanks for your help - one of my forms has three combos and
the other has four - Im going to try to figure this out with
the three combos (I assume there is 12 tests with three combos)
....

Cheers
GLT

Nope, there are 9 groups with 3 fields and 16 with 4, but the
three fields has three fields in each of the nine combinations,
the four box set has 16 combinations of four fields.

Here is the where clause for the four box combination: please
don't ask for five comboboxes.

WHERE
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapTyp
)) Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapTyp
)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapTyp
)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And ((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapTyp
)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)= forms!frm_TAPMAN_StkTapDet!
cmb_TapTyp))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And ((tbl_TAPMAN_StkTapDet.Manuf)= "<All>" ) And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapTyp
)) Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)= "<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapTyp
)) Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And ((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)="<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)=forms!frm_TAPMAN_StkTapDet! cmb_TapTyp
)) Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)=forms!frm_TAPMAN_StkTapDet! cmb_BrdNa
m) And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)= "<All>") And
((tbl_TAPMAN_StkTapDet.Use)=forms!frm_TAPMAN_StkTapDet!cmb_Use)
And ((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)= "<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)=forms!frm_TAPMAN_StkTapDet! cmb_Site)
And
((tbl_TAPMAN_StkTapDet.Use)= "<All>") And
((tbl_TAPMAN_StkTapDet.Manuf)="<All>") And
((tbl_TAPMAN_StkTapDet.Type)="<All>"))
Or
(((tbl_TAPMAN_StkTapDet.Site)="<All>" And
((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>")) And
(((forms!frm_TAPMAN_StkTapDet!cmb_Use)="<All>") And
((forms!frm_TAPMAN_StkTapDet!cmb_Site)="<All>"))
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter subform somehow! 6
Cascading Dependant combo boxes 0
Combo box in multiple tables within a database 4
Combo Box Question 4
Blank combo or list box 1
Cascading ComboBox on SubForm 6
Cascading Combo Box Questions 7
Combo Box Criteria Question 1

Top