Change criteria based off of Option Group

  • Thread starter Thread starter Matt P
  • Start date Start date
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
 
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).
 
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
 
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
 
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...
 
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));
 
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!
 
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 :)
 
Back
Top