Multiple check boxes on form determine criteria in query

[Matt P]

Hello, I have a form that has four check boxes and all of them work
fine except for the last one which is to not exclude anything. Here is
my sql:

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"))) OR (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkWI],"WI"))) OR (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkIL],"IL"))) OR (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkAll],([tblCompanies].[State]) Like "*")));


Not sure what the syntax of the last one should be...

Thanks!

Matt P

 

[KARL DEWEY]

Try this --
WHERE (tblCompanies.State = IIf([FORMS]![frmMailingList]![chkMN],"MN")) OR
(tblCompanies.State = IIf([FORMS]![frmMailingList]![chkWI],"WI")) OR
(tblCompanies.State = IIf([FORMS]![frmMailingList]![chkIL],"IL")) OR
(tblCompanies.State LIKE IIf([FORMS]![frmMailingList]![chkAll], "*");

 

[Marshall Barton]

Hello, I have a form that has four check boxes and all of them work
fine except for the last one which is to not exclude anything. Here is
my sql:

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"))) OR (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkWI],"WI"))) OR (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkIL],"IL"))) OR (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkAll],([tblCompanies].[State]) Like "*")));

Your IIf really should have the third argument. What you
have will work, but it's only because of subtle reasons that
you (or the next programmer) may not understand.

You can not use Like when you are using =

I think it would be better to write you where clause this
way:

WHERE
(tblCompanies.State=IIf(FORMS!frmMailingList!chkMN,"MN","XX")
OR
(tblCompanies.State=IIf(FORMS!frmMailingList!chkWI,"WI","XX")
OR
(tblCompanies.State=IIf(FORMS!frmMailingList!chkIL,"IL","XX")
OR FORMS!frmMailingList!chkAll

or, maybe even clearer:

WHERE (FORMS!frmMailingList!chkMN
And tblCompanies.State="MN")
OR (FORMS!frmMailingList!chkWI
And tblCompanies.State="WI")
OR (FORMS!frmMailingList!chkIL
And tblCompanies.State="IL")
OR FORMS!frmMailingList!chkAll

 

[Matt P]

Try this --
WHERE (tblCompanies.State = IIf([FORMS]![frmMailingList]![chkMN],"MN"))OR
(tblCompanies.State = IIf([FORMS]![frmMailingList]![chkWI],"WI")) OR
(tblCompanies.State = IIf([FORMS]![frmMailingList]![chkIL],"IL")) OR
(tblCompanies.State LIKE IIf([FORMS]![frmMailingList]![chkAll], "*");

Hello, I have a form that has four check boxes and all of them work
fine except for the last one which is to not exclude anything. Here is
my sql:

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"))) OR (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkWI],"WI"))) OR (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkIL],"IL"))) OR (((tblCompanies.State)=IIf([FORMS]![frmMailingList]!
[chkAll],([tblCompanies].[State]) Like "*")));

Not sure what the syntax of the last one should be...

Matt P

Thanks worked perfect, finally I understand how to use the like
operator!