Change criteria based off of Option Group

M

Matt P

Alright I have a form with various things to manipulate the criteria
in the query. Everything works except for the option group which has
3 options. The first just leaves all the records in which works fine,
the second "SHOULD" remove all of the records that have No. And the
3rd "SHOULD" only have the records that have Yes. Anyone see where I
am going wrong in my query?

SELECT tblCompanies.[Company Name], tblAdjusters.[Last Name],
tblAdjusters.[First Name], tblAdjusters.Address, tblCompanies.City,
tblCompanies.State, tblAdjusters.ZipCode, tblCompanies.Show,
tblAdjusters.Save, tblCompanies.Independent
FROM tblCompanies INNER JOIN tblAdjusters ON
tblCompanies.ID=tblAdjusters.CompanyNumber
WHERE (((tblCompanies.State)=IIf(FORMS!frmMailingList!chkWI,"WI"))) Or
(((tblCompanies.State)=IIf(FORMS!frmMailingList!chkIL,"IL"))) Or
(((tblCompanies.State)=IIf(FORMS!frmMailingList!chkMN,"MN"))) And
((tblCompanies.Show)=Yes) And ((tblAdjusters.Save)=Yes) And
(((tblCompanies.Independent)=IIf(FORMS!frmMailingList!
opgIndAdj=2,No))) Or (((tblCompanies.Independent)=IIf(FORMS!
frmMailingList!opgIndAdj=3,Yes)));

Thanks,
Matt P
 
P

Piet Linden

Alright I have a form with various things to manipulate the criteria
in the query.  Everything works except for the option group which has
3 options.  The first just leaves all the records in which works fine,
the second "SHOULD" remove all of the records that have No.  And the
3rd "SHOULD" only have the records that have Yes. Anyone see where I
am going wrong in my query?

SELECT tblCompanies.[Company Name], tblAdjusters.[Last Name],
tblAdjusters.[First Name], tblAdjusters.Address, tblCompanies.City,
tblCompanies.State, tblAdjusters.ZipCode, tblCompanies.Show,
tblAdjusters.Save, tblCompanies.Independent
FROM tblCompanies INNER JOIN tblAdjusters ON
tblCompanies.ID=tblAdjusters.CompanyNumber
WHERE (((tblCompanies.State)=IIf(FORMS!frmMailingList!chkWI,"WI"))) Or
(((tblCompanies.State)=IIf(FORMS!frmMailingList!chkIL,"IL"))) Or
(((tblCompanies.State)=IIf(FORMS!frmMailingList!chkMN,"MN"))) And
((tblCompanies.Show)=Yes) And ((tblAdjusters.Save)=Yes) And
(((tblCompanies.Independent)=IIf(FORMS!frmMailingList!
opgIndAdj=2,No))) Or (((tblCompanies.Independent)=IIf(FORMS!
frmMailingList!opgIndAdj=3,Yes)));

Thanks,
Matt P
Click to expand...

Maybe the simple answer is to use a dropdown for what appears to have
3 possible values: 1/Yes, 0/No, Null (not answered).
 
J

John Spencer MVP

And (tblCompanies.Independent=
IIf(FORMS!frmMailingList!opgIndAdj=2,False,
IIf(FORMS!frmMailingList!opgIndAdj=3,True))
OR FORMS!frmMailingList!opgIndAdj=1)


WHERE tblCompanies.State=IIf(FORMS!frmMailingList!chkWI,"WI" Or
tblCompanies.State=IIf(FORMS!frmMailingList!chkIL,"IL" Or
tblCompanies.State=IIf(FORMS!frmMailingList!chkMN,"MN")
And tblCompanies.Show=True
And tblAdjusters.Save=True

And (tblCompanies.Independent=
IIf(FORMS!frmMailingList!opgIndAdj=2,False,
IIf(FORMS!frmMailingList!opgIndAdj=3,True))
OR FORMS!frmMailingList!opgIndAdj=1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
M

Matt P

Alright I have a form with various things to manipulate the criteria
in the query.  Everything works except for the option group which has
3 options.  The first just leaves all the records in which works fine,
the second "SHOULD" remove all of the records that have No.  And the
3rd "SHOULD" only have the records that have Yes. Anyone see where I
am going wrong in my query?
Click to expand...
SELECT tblCompanies.[Company Name], tblAdjusters.[Last Name],
tblAdjusters.[First Name], tblAdjusters.Address, tblCompanies.City,
tblCompanies.State, tblAdjusters.ZipCode, tblCompanies.Show,
tblAdjusters.Save, tblCompanies.Independent
FROM tblCompanies INNER JOIN tblAdjusters ON
tblCompanies.ID=tblAdjusters.CompanyNumber
WHERE (((tblCompanies.State)=IIf(FORMS!frmMailingList!chkWI,"WI"))) Or
(((tblCompanies.State)=IIf(FORMS!frmMailingList!chkIL,"IL"))) Or
(((tblCompanies.State)=IIf(FORMS!frmMailingList!chkMN,"MN"))) And
((tblCompanies.Show)=Yes) And ((tblAdjusters.Save)=Yes) And
(((tblCompanies.Independent)=IIf(FORMS!frmMailingList!
opgIndAdj=2,No))) Or (((tblCompanies.Independent)=IIf(FORMS!
frmMailingList!opgIndAdj=3,Yes)));
Click to expand...
Thanks,
Matt P
Click to expand...

Maybe the simple answer is to use a dropdown for what appears to have
3 possible values: 1/Yes, 0/No, Null (not answered).
Click to expand...

Thanks for the response! Actually I almost achieved the desired
results with the query:

SELECT tblCompanies.[Company Name],
tblCompanies.State,
tblCompanies.City,
tblCompanies.Show,
tblCompanies.Independent,
tblAdjusters.Save,
tblAdjusters.Address,
tblAdjusters.ZipCode,
tblAdjusters.[Last Name],
tblAdjusters.[First Name]
FROM tblCompanies INNER JOIN tblAdjusters ON tblCompanies.ID =
tblAdjusters.CompanyNumber
WHERE (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkMN],"MN")) AND
((tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=2,No) Or
(tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=3,Yes))) OR
(((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkWI],"WI")) AND
((tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=2,No) Or
(tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=3,Yes))) OR
(((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkIL],"IL")) AND
((tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=2,No) Or
(tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=3,Yes)));


The only thing I still need to fix is if they select the first option
which is basically to include Yes and No... any ideas why this isn't
working the way it is and what I need to do?


Thanks,
Matt P
 
J

John Spencer MVP

Whoops! removed too many parentheses


WHERE tblCompanies.State=IIf(FORMS!frmMailingList!chkWI,"WI") Or
tblCompanies.State=IIf(FORMS!frmMailingList!chkIL,"IL") Or
tblCompanies.State=IIf(FORMS!frmMailingList!chkMN,"MN"))
And tblCompanies.Show=True
And tblAdjusters.Save=True

And (tblCompanies.Independent=
IIf(FORMS!frmMailingList!opgIndAdj=2,False,
IIf(FORMS!frmMailingList!opgIndAdj=3,True))
OR FORMS!frmMailingList!opgIndAdj=1)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
And (tblCompanies.Independent=
IIf(FORMS!frmMailingList!opgIndAdj=2,False,
IIf(FORMS!frmMailingList!opgIndAdj=3,True))
OR FORMS!frmMailingList!opgIndAdj=1)


WHERE tblCompanies.State=IIf(FORMS!frmMailingList!chkWI,"WI" Or
tblCompanies.State=IIf(FORMS!frmMailingList!chkIL,"IL" Or
tblCompanies.State=IIf(FORMS!frmMailingList!chkMN,"MN")
And tblCompanies.Show=True
And tblAdjusters.Save=True

And (tblCompanies.Independent=
IIf(FORMS!frmMailingList!opgIndAdj=2,False,
IIf(FORMS!frmMailingList!opgIndAdj=3,True))
OR FORMS!frmMailingList!opgIndAdj=1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Matt said:
Alright I have a form with various things to manipulate the criteria
in the query. Everything works except for the option group which has
3 options. The first just leaves all the records in which works fine,
the second "SHOULD" remove all of the records that have No. And the
3rd "SHOULD" only have the records that have Yes. Anyone see where I
am going wrong in my query?

SELECT tblCompanies.[Company Name], tblAdjusters.[Last Name],
tblAdjusters.[First Name], tblAdjusters.Address, tblCompanies.City,
tblCompanies.State, tblAdjusters.ZipCode, tblCompanies.Show,
tblAdjusters.Save, tblCompanies.Independent
FROM tblCompanies INNER JOIN tblAdjusters ON
tblCompanies.ID=tblAdjusters.CompanyNumber
WHERE (((tblCompanies.State)=IIf(FORMS!frmMailingList!chkWI,"WI"))) Or
(((tblCompanies.State)=IIf(FORMS!frmMailingList!chkIL,"IL"))) Or
(((tblCompanies.State)=IIf(FORMS!frmMailingList!chkMN,"MN"))) And
((tblCompanies.Show)=Yes) And ((tblAdjusters.Save)=Yes) And
(((tblCompanies.Independent)=IIf(FORMS!frmMailingList!
opgIndAdj=2,No))) Or (((tblCompanies.Independent)=IIf(FORMS!
frmMailingList!opgIndAdj=3,Yes)));

Thanks,
Matt P
Click to expand...
Click to expand...
 
M

Matt P

And (tblCompanies.Independent=
   IIf(FORMS!frmMailingList!opgIndAdj=2,False,
   IIf(FORMS!frmMailingList!opgIndAdj=3,True))
   OR FORMS!frmMailingList!opgIndAdj=1)

WHERE tblCompanies.State=IIf(FORMS!frmMailingList!chkWI,"WI" Or
       tblCompanies.State=IIf(FORMS!frmMailingList!chkIL,"IL" Or
       tblCompanies.State=IIf(FORMS!frmMailingList!chkMN,"MN")
  And tblCompanies.Show=True
  And tblAdjusters.Save=True

  And (tblCompanies.Independent=
   IIf(FORMS!frmMailingList!opgIndAdj=2,False,
   IIf(FORMS!frmMailingList!opgIndAdj=3,True))
   OR FORMS!frmMailingList!opgIndAdj=1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Matt said:
Alright I have a form with various things to manipulate the criteria
in the query.  Everything works except for the option group which has
3 options.  The first just leaves all the records in which works fine,
the second "SHOULD" remove all of the records that have No.  And the
3rd "SHOULD" only have the records that have Yes. Anyone see where I
am going wrong in my query?
Click to expand...
SELECT tblCompanies.[Company Name], tblAdjusters.[Last Name],
tblAdjusters.[First Name], tblAdjusters.Address, tblCompanies.City,
tblCompanies.State, tblAdjusters.ZipCode, tblCompanies.Show,
tblAdjusters.Save, tblCompanies.Independent
FROM tblCompanies INNER JOIN tblAdjusters ON
tblCompanies.ID=tblAdjusters.CompanyNumber
WHERE (((tblCompanies.State)=IIf(FORMS!frmMailingList!chkWI,"WI"))) Or
(((tblCompanies.State)=IIf(FORMS!frmMailingList!chkIL,"IL"))) Or
(((tblCompanies.State)=IIf(FORMS!frmMailingList!chkMN,"MN"))) And
((tblCompanies.Show)=Yes) And ((tblAdjusters.Save)=Yes) And
(((tblCompanies.Independent)=IIf(FORMS!frmMailingList!
opgIndAdj=2,No))) Or (((tblCompanies.Independent)=IIf(FORMS!
frmMailingList!opgIndAdj=3,Yes)));
Click to expand...
Thanks,
Matt P
Click to expand...
Click to expand...

Oh wow thanks! So simple all I had to do was add "Or [FORMS]!
[frmMailingList]![opgIndAdj]=1" to the criteria! So my final code
ended up being:

SELECT tblCompanies.[Company Name],
tblCompanies.State,
tblCompanies.City,
tblCompanies.Show,
tblCompanies.Independent,
tblAdjusters.Save,
tblAdjusters.Address,
tblAdjusters.ZipCode,
tblAdjusters.[Last Name],
tblAdjusters.[First Name]
FROM tblCompanies INNER JOIN tblAdjusters ON tblCompanies.ID =
tblAdjusters.CompanyNumber
WHERE (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkMN],"MN")) AND
((tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=2,No) Or
(tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=3,Yes) Or
[FORMS]![frmMailingList]![opgIndAdj]=1)) OR
(((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkWI],"WI")) AND
((tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=2,No) Or
(tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=3,Yes) Or
[FORMS]![frmMailingList]![opgIndAdj]=1)) OR
(((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkIL],"IL")) AND
((tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=2,No) Or
(tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=3,Yes) Or
[FORMS]![frmMailingList]![opgIndAdj]=1));
 
M

Matt P

And (tblCompanies.Independent=
   IIf(FORMS!frmMailingList!opgIndAdj=2,False,
   IIf(FORMS!frmMailingList!opgIndAdj=3,True))
   OR FORMS!frmMailingList!opgIndAdj=1)
Click to expand...
WHERE tblCompanies.State=IIf(FORMS!frmMailingList!chkWI,"WI" Or
       tblCompanies.State=IIf(FORMS!frmMailingList!chkIL,"IL"Or
       tblCompanies.State=IIf(FORMS!frmMailingList!chkMN,"MN")
  And tblCompanies.Show=True
  And tblAdjusters.Save=True
Click to expand...
  And (tblCompanies.Independent=
   IIf(FORMS!frmMailingList!opgIndAdj=2,False,
   IIf(FORMS!frmMailingList!opgIndAdj=3,True))
   OR FORMS!frmMailingList!opgIndAdj=1)
Click to expand...
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Matt said:
Alright I have a form with various things to manipulate the criteria
in the query.  Everything works except for the option group which has
3 options.  The first just leaves all the records in which works fine,
the second "SHOULD" remove all of the records that have No.  And the
3rd "SHOULD" only have the records that have Yes. Anyone see where I
am going wrong in my query?
SELECT tblCompanies.[Company Name], tblAdjusters.[Last Name],
tblAdjusters.[First Name], tblAdjusters.Address, tblCompanies.City,
tblCompanies.State, tblAdjusters.ZipCode, tblCompanies.Show,
tblAdjusters.Save, tblCompanies.Independent
FROM tblCompanies INNER JOIN tblAdjusters ON
tblCompanies.ID=tblAdjusters.CompanyNumber
WHERE (((tblCompanies.State)=IIf(FORMS!frmMailingList!chkWI,"WI")))Or
(((tblCompanies.State)=IIf(FORMS!frmMailingList!chkIL,"IL"))) Or
(((tblCompanies.State)=IIf(FORMS!frmMailingList!chkMN,"MN"))) And
((tblCompanies.Show)=Yes) And ((tblAdjusters.Save)=Yes) And
(((tblCompanies.Independent)=IIf(FORMS!frmMailingList!
opgIndAdj=2,No))) Or (((tblCompanies.Independent)=IIf(FORMS!
frmMailingList!opgIndAdj=3,Yes)));
Thanks,
Matt P
Click to expand...
Click to expand...

Oh wow thanks!  So simple all I had to do was add "Or [FORMS]!
[frmMailingList]![opgIndAdj]=1" to the criteria! So my final code
ended up being:

SELECT tblCompanies.[Company Name],
       tblCompanies.State,
       tblCompanies.City,
       tblCompanies.Show,
       tblCompanies.Independent,
       tblAdjusters.Save,
       tblAdjusters.Address,
       tblAdjusters.ZipCode,
       tblAdjusters.[Last Name],
       tblAdjusters.[First Name]
  FROM tblCompanies INNER JOIN tblAdjusters ON tblCompanies.ID =
tblAdjusters.CompanyNumber
 WHERE (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkMN],"MN")) AND
       ((tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=2,No) Or
       (tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=3,Yes) Or
       [FORMS]![frmMailingList]![opgIndAdj]=1)) OR
       (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkWI],"WI")) AND
       ((tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=2,No) Or
       (tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=3,Yes) Or
       [FORMS]![frmMailingList]![opgIndAdj]=1)) OR
       (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkIL],"IL")) AND
       ((tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=2,No) Or
       (tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=3,Yes) Or
       [FORMS]![frmMailingList]![opgIndAdj]=1));
Click to expand...

Oh wow I just tried your code John... thats way better than my chop
job! Thanks for showing me the simpler more efficient way!
 
M

Matt P

And (tblCompanies.Independent=
   IIf(FORMS!frmMailingList!opgIndAdj=2,False,
   IIf(FORMS!frmMailingList!opgIndAdj=3,True))
   OR FORMS!frmMailingList!opgIndAdj=1)
WHERE tblCompanies.State=IIf(FORMS!frmMailingList!chkWI,"WI" Or
       tblCompanies.State=IIf(FORMS!frmMailingList!chkIL,"IL" Or
       tblCompanies.State=IIf(FORMS!frmMailingList!chkMN,"MN")
  And tblCompanies.Show=True
  And tblAdjusters.Save=True
  And (tblCompanies.Independent=
   IIf(FORMS!frmMailingList!opgIndAdj=2,False,
   IIf(FORMS!frmMailingList!opgIndAdj=3,True))
   OR FORMS!frmMailingList!opgIndAdj=1)
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Matt P wrote:
Alright I have a form with various things to manipulate the criteria
in the query.  Everything works except for the option group whichhas
3 options.  The first just leaves all the records in which works fine,
the second "SHOULD" remove all of the records that have No.  And the
3rd "SHOULD" only have the records that have Yes. Anyone see where I
am going wrong in my query?
SELECT tblCompanies.[Company Name], tblAdjusters.[Last Name],
tblAdjusters.[First Name], tblAdjusters.Address, tblCompanies.City,
tblCompanies.State, tblAdjusters.ZipCode, tblCompanies.Show,
tblAdjusters.Save, tblCompanies.Independent
FROM tblCompanies INNER JOIN tblAdjusters ON
tblCompanies.ID=tblAdjusters.CompanyNumber
WHERE (((tblCompanies.State)=IIf(FORMS!frmMailingList!chkWI,"WI"))) Or
(((tblCompanies.State)=IIf(FORMS!frmMailingList!chkIL,"IL"))) Or
(((tblCompanies.State)=IIf(FORMS!frmMailingList!chkMN,"MN"))) And
((tblCompanies.Show)=Yes) And ((tblAdjusters.Save)=Yes) And
(((tblCompanies.Independent)=IIf(FORMS!frmMailingList!
opgIndAdj=2,No))) Or (((tblCompanies.Independent)=IIf(FORMS!
frmMailingList!opgIndAdj=3,Yes)));
Thanks,
Matt P
Click to expand...
Click to expand...
Oh wow thanks!  So simple all I had to do was add "Or [FORMS]!
[frmMailingList]![opgIndAdj]=1" to the criteria! So my final code
ended up being:
Click to expand...
SELECT tblCompanies.[Company Name],
       tblCompanies.State,
       tblCompanies.City,
       tblCompanies.Show,
       tblCompanies.Independent,
       tblAdjusters.Save,
       tblAdjusters.Address,
       tblAdjusters.ZipCode,
       tblAdjusters.[Last Name],
       tblAdjusters.[First Name]
  FROM tblCompanies INNER JOIN tblAdjusters ON tblCompanies.ID =
tblAdjusters.CompanyNumber
 WHERE (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkMN],"MN")) AND
       ((tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=2,No) Or
       (tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=3,Yes) Or
       [FORMS]![frmMailingList]![opgIndAdj]=1)) OR
       (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkWI],"WI")) AND
       ((tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=2,No) Or
       (tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=3,Yes) Or
       [FORMS]![frmMailingList]![opgIndAdj]=1)) OR
       (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkIL],"IL")) AND
       ((tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=2,No) Or
       (tblCompanies.Independent)=IIf([FORMS]![frmMailingList]!
[opgIndAdj]=3,Yes) Or
       [FORMS]![frmMailingList]![opgIndAdj]=1));
Click to expand...

Oh wow I just tried your code John... thats way better than my chop
job!  Thanks for showing me the simpler more efficient way!
Click to expand...

Oh wait I was testing your sql and it almost did everything except for
when it came to option 3... it seemed to include all of them.
Switched back to my sql and it worked fine... but thanks you showed me
how to get Option 1 working :)
 

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

Multiple check boxes on form determine criteria in query 3
Change criteria from option group 2
Using Option Group for Criteria of query 2
query only shows some records of given criteria 1
Option Buttons to Pass Criteria to Query 6
Options Group and Query Criteria, revisited 3
Creating parameter queries based on a form with multiple criteria 2
Require help with Option Group 2

Top