Need help with an SQL statement

[chead5]

I have this SQL statement

SELECT Count(DownloadN ew.AcctNumb) AS CountOfAcctNumb
FROM DownloadNew
WHERE (((DownloadNew. Phase)="0001") AND
((DownloadNew.F upDte)<=[Forms]![Reconciliation Forms]![Combo27]) AND
((DownloadNew.R outer)="RPP") AND ((DownloadNew.D PD)<45) AND
((DownloadNew.S vcBrnd)="PD") AND ((DownloadNew.L abel) Not Like "JC" And
(DownloadNew.La bel) Not Like "LA" And (DownloadNew.La bel) Not Like
"VC")) OR (((DownloadNew. Phase)="0001") AND
((DownloadNew.F upDte)<=[Forms]![Reconciliation Forms]![Combo27]) AND
((DownloadNew.R outer) Not Like "RPP") AND ((DownloadNew.S vcBrnd)="PD")
AND ((DownloadNew.L abel) Not Like "JC" And (DownloadNew.La bel) Not Like
"LA" And (DownloadNew.La bel) Not Like "VC"));

can anyone tell me how to exclued the following items

I need to exclude the following
Anything from the DownloadNew table that has a "state" value of "MA"
AND a null in the "Usrgrp"

[MGFoster]

Re: Need help with an SQL statement

chead5 wrote:[color=blue]
> I have this SQL statement
>
> SELECT Count(DownloadN ew.AcctNumb) AS CountOfAcctNumb
> FROM DownloadNew
> WHERE (((DownloadNew. Phase)="0001") AND
> ((DownloadNew.F upDte)<=[Forms]![Reconciliation Forms]![Combo27]) AND
> ((DownloadNew.R outer)="RPP") AND ((DownloadNew.D PD)<45) AND
> ((DownloadNew.S vcBrnd)="PD") AND ((DownloadNew.L abel) Not Like "JC" And
> (DownloadNew.La bel) Not Like "LA" And (DownloadNew.La bel) Not Like
> "VC")) OR (((DownloadNew. Phase)="0001") AND
> ((DownloadNew.F upDte)<=[Forms]![Reconciliation Forms]![Combo27]) AND
> ((DownloadNew.R outer) Not Like "RPP") AND ((DownloadNew.S vcBrnd)="PD")
> AND ((DownloadNew.L abel) Not Like "JC" And (DownloadNew.La bel) Not Like
> "LA" And (DownloadNew.La bel) Not Like "VC"));
>
> can anyone tell me how to exclued the following items
>
> I need to exclude the following
> Anything from the DownloadNew table that has a "state" value of "MA"
> AND a null in the "Usrgrp"
>[/color]

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

I hate all the parentheses Access puts around the items in the WHERE
clause. It can screw up the Boolean logic. Here's a clean(er) version.

WHERE [state] <> "MA"
AND UsrGrp IS NOT NULL
AND Phase="0001"
AND FupDte<=[Forms]![Reconciliation Forms]![Combo27]
AND Router="RPP"
AND DPD<45
AND SvcBrnd="PD"
AND [Label] Not In ("JC", "LA", "VC")

You were using LIKE incorrectly. It is only to be used when you want to
search the partial string in a longer string. When you don't have any
wildcards then the expression is = or <>. I changed your NOT LIKEs to
one IN () predicate, which translates to:

Label does not equal any of the letter groups inside the parentheses.

Your OR clause was a repeat of the previous criteria, so I got rid of
it.

I put brackets [] around state and Label 'cuz they are SQL reserved
words.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRKLKK4echKq OuFEgEQKBtwCcDz w/TEo6C0+LgmsDyiO 0HFMSfigAnRxU
Y+NqGoEi0KG0odr QYL4f8zPF
=n2ke
-----END PGP SIGNATURE-----