Multiple check boxes on form determine criteria in query

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
 
K

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

MGFoster

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


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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

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

If that doesn't work try using "%" as the wildcard instead of "*".

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSjA+5YechKqOuFEgEQJP9QCfT6jCAjjtRW4e+P8+GkLkX1K2DDIAn0AX
TCTTdmZK8TIX6zSjRPv3XGtG
=0por
-----END PGP SIGNATURE-----
 
M

Marshall Barton

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


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
 
M

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 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:
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!
 

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

Top