Multiple check boxes on form determine criteria in query

  • Thread starter Thread starter Matt P
  • Start date Start date
M

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
 
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], "*");
 
Matt said:
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 "*")));
Click to expand...


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
 
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], "*");

Matt P said:
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:
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"))) 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 "*")));
Click to expand...
Not sure what the syntax of the last one should be...

Matt P
Click to expand...
Click to expand...

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