Query with more than 8 'OR' criteria?

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I’m working on a prickly little query now. I have values of “-1†and “0†in
a table. I have 20 CheckBoxes on a form. If I check a CheckBox, I and there
is a “-1†in the table, I get a match, and it shows up in my query. Great!
If I don’t check a box, it doesn’t show up in my query. Great! I can go
into the query view and on the criteria row, I put something like this:
“[Forms]![frmMstr]![chkOther1]†and “[Forms]![frmMstr]![chkOther2]â€. In my
table I have a five “-1†for chkOther1 and five “-1†for chkOther2, with 4
rows the same and two rows different, so really there are 6 rows with a “-1â€.
I’d like to put a check in the CheckBox for “[Forms]![frmMstr]![chkOther1]â€
and “[Forms]![frmMstr]![chkOther2]†and see all 6 records. Make sense? I
can see that there are 8 rows in query view; I can put the CheckBoxes all on
one row and then stagger the “-1†criteria, but NOT on the same row. Problem
is that I run out at 8, and I have 20 criteria that I have to search for with
CheckBoxes. Please tell me there is an easy way to overcome this. The SQL
is shown below, but it won’t compile (unless I use only 8 OR criteria with
the “-1†value).

HAVING (((tblStocksGroup.HDVest50k)=[Forms]![frmMstr]![chkHDVest50k]) AND
((tblStocksGroup.HDVest100k)=[Forms]![frmMstr]![chkHDVest100k]) AND
((tblStocksGroup.ETF)=[Forms]![frmMstr]![chkETF]) AND
((tblStocksGroup.NetJets)=[Forms]![frmMstr]![chkNetJets]) AND
((tblStocksGroup.JetBlue)=[Forms]![frmMstr]![chkJetBlue]) AND
((tblStocksGroup.JetBlueTradeLink)=[Forms]![frmMstr]![chkJetBlueTradeLink])
AND ((tblStocksGroup.AirWisconsin)=[Forms]![frmMstr]![chkAirWisconsin]) AND
((tblStocksGroup.Alaska)=[Forms]![frmMstr]![chkAlaska]) AND
((tblStocksGroup.American)=[Forms]![frmMstr]![chkAmerican]) AND
((tblStocksGroup.Continental)=[Forms]![frmMstr]![chkContinental]) AND
((tblStocksGroup.Delta)=[Forms]![frmMstr]![chkDelta]) AND
((tblStocksGroup.Frontier)=[Forms]![frmMstr]![chkFrontier]) AND
((tblStocksGroup.Southwest)=[Forms]![frmMstr]![chkSouthwest]) AND
((tblStocksGroup.UnitedFAS)=[Forms]![frmMstr]![chkUnitedFAS]) AND
((tblStocksGroup.UnitedPilots)=[Forms]![frmMstr]![chkUnitedPilots]) AND
((tblStocksGroup.WorldAirway)=[Forms]![frmMstr]![chkWorldAirway]) AND
((tblStocksGroup.LeveragedLong)=[Forms]![frmMstr]![chkLeveragedLong]) AND
((tblStocksGroup.LeveragedShort)=[Forms]![frmMstr]![chkLeveragedShort]) AND
((tblStocksGroup.Other1)=[Forms]![frmMstr]![chkOther1]) AND
((tblStocksGroup.Other2)=[Forms]![frmMstr]![chkOther2])) OR
(((tblStocksGroup.HDVest50k)="-1")) OR (((tblStocksGroup.HDVest100k)="-1"))
OR (((tblStocksGroup.ETF)="-1")) OR (((tblStocksGroup.NetJets)="-1")) OR
(((tblStocksGroup.JetBlue)="-1")) OR
(((tblStocksGroup.JetBlueTradeLink)="-1")) OR
(((tblStocksGroup.AirWisconsin)="-1")) OR (((tblStocksGroup.Alaska)="-1")) OR
(((tblStocksGroup. American)="-1")) OR (((tblStocksGroup. Continental)="-1"))
OR (((tblStocksGroup. Delta)="-1")) OR (((tblStocksGroup. Frontier)="-1")) OR
(((tblStocksGroup Southwest)="-1")) OR (((tblStocksGroup. UnitedFAS)="-1"))
OR (((tblStocksGroup. UnitedPilots)="-1")) OR (((tblStocksGroup.
WorldAirway)="-1")) OR (((tblStocksGroup. LeveragedLong)="-1")) OR
(((tblStocksGroup. LeveragedShort)="-1")) OR (((tblStocksGroup.
Other1)="-1")) OR (((tblStocksGroup. Other2)="-1"));

Thanks!
Ryan--
 
ryguy7272 said:
I’m working on a prickly little query now. I have values of “-1†and “0â€
in
a table. I have 20 CheckBoxes on a form. If I check a CheckBox, I and [snip]
Problem is that I run out at 8, and I have 20 criteria that I have to
search for with
CheckBoxes. Please tell me there is an easy way to overcome this. The
SQL is shown below, but it won’t compile (unless I use only 8 OR criteria
with the “-1†value).
Click to expand...

More criteria rows can be added by using insert - rows from the menu.

Your actual (and larger) problem is your table design. You need a properly
designed database and what you have is a spreadsheet. Your individual
stocks should be stored in rows, not fields.

StockName StockValue
HDVest50k 50.12
ETF 104.23
etc..
 
Problem is that I run out at 8, and I have 20 criteria that I have to
search for with CheckBoxes.
You can add more rows by dragging the area larger and cursoring down.

i did not follow what you are trying to do. Can you explain the business
rule you wish to apply?

--
Build a little, test a little.


ryguy7272 said:
I’m working on a prickly little query now. I have values of “-1†and “0†in
a table. I have 20 CheckBoxes on a form. If I check a CheckBox, I and there
is a “-1†in the table, I get a match, and it shows up in my query. Great!
If I don’t check a box, it doesn’t show up in my query. Great! I can go
into the query view and on the criteria row, I put something like this:
“[Forms]![frmMstr]![chkOther1]†and “[Forms]![frmMstr]![chkOther2]â€. In my
table I have a five “-1†for chkOther1 and five “-1†for chkOther2, with 4
rows the same and two rows different, so really there are 6 rows with a “-1â€.
I’d like to put a check in the CheckBox for “[Forms]![frmMstr]![chkOther1]â€
and “[Forms]![frmMstr]![chkOther2]†and see all 6 records. Make sense? I
can see that there are 8 rows in query view; I can put the CheckBoxes all on
one row and then stagger the “-1†criteria, but NOT on the same row. Problem
is that I run out at 8, and I have 20 criteria that I have to search for with
CheckBoxes. Please tell me there is an easy way to overcome this. The SQL
is shown below, but it won’t compile (unless I use only 8 OR criteria with
the “-1†value).

HAVING (((tblStocksGroup.HDVest50k)=[Forms]![frmMstr]![chkHDVest50k]) AND
((tblStocksGroup.HDVest100k)=[Forms]![frmMstr]![chkHDVest100k]) AND
((tblStocksGroup.ETF)=[Forms]![frmMstr]![chkETF]) AND
((tblStocksGroup.NetJets)=[Forms]![frmMstr]![chkNetJets]) AND
((tblStocksGroup.JetBlue)=[Forms]![frmMstr]![chkJetBlue]) AND
((tblStocksGroup.JetBlueTradeLink)=[Forms]![frmMstr]![chkJetBlueTradeLink])
AND ((tblStocksGroup.AirWisconsin)=[Forms]![frmMstr]![chkAirWisconsin]) AND
((tblStocksGroup.Alaska)=[Forms]![frmMstr]![chkAlaska]) AND
((tblStocksGroup.American)=[Forms]![frmMstr]![chkAmerican]) AND
((tblStocksGroup.Continental)=[Forms]![frmMstr]![chkContinental]) AND
((tblStocksGroup.Delta)=[Forms]![frmMstr]![chkDelta]) AND
((tblStocksGroup.Frontier)=[Forms]![frmMstr]![chkFrontier]) AND
((tblStocksGroup.Southwest)=[Forms]![frmMstr]![chkSouthwest]) AND
((tblStocksGroup.UnitedFAS)=[Forms]![frmMstr]![chkUnitedFAS]) AND
((tblStocksGroup.UnitedPilots)=[Forms]![frmMstr]![chkUnitedPilots]) AND
((tblStocksGroup.WorldAirway)=[Forms]![frmMstr]![chkWorldAirway]) AND
((tblStocksGroup.LeveragedLong)=[Forms]![frmMstr]![chkLeveragedLong]) AND
((tblStocksGroup.LeveragedShort)=[Forms]![frmMstr]![chkLeveragedShort]) AND
((tblStocksGroup.Other1)=[Forms]![frmMstr]![chkOther1]) AND
((tblStocksGroup.Other2)=[Forms]![frmMstr]![chkOther2])) OR
(((tblStocksGroup.HDVest50k)="-1")) OR (((tblStocksGroup.HDVest100k)="-1"))
OR (((tblStocksGroup.ETF)="-1")) OR (((tblStocksGroup.NetJets)="-1")) OR
(((tblStocksGroup.JetBlue)="-1")) OR
(((tblStocksGroup.JetBlueTradeLink)="-1")) OR
(((tblStocksGroup.AirWisconsin)="-1")) OR (((tblStocksGroup.Alaska)="-1")) OR
(((tblStocksGroup. American)="-1")) OR (((tblStocksGroup. Continental)="-1"))
OR (((tblStocksGroup. Delta)="-1")) OR (((tblStocksGroup. Frontier)="-1")) OR
(((tblStocksGroup Southwest)="-1")) OR (((tblStocksGroup. UnitedFAS)="-1"))
OR (((tblStocksGroup. UnitedPilots)="-1")) OR (((tblStocksGroup.
WorldAirway)="-1")) OR (((tblStocksGroup. LeveragedLong)="-1")) OR
(((tblStocksGroup. LeveragedShort)="-1")) OR (((tblStocksGroup.
Other1)="-1")) OR (((tblStocksGroup. Other2)="-1"));

Thanks!
Ryan--
Click to expand...
 
Back
Top