[AccessD] Fwd: Multiple Criteria Query

David McAfee davidmcafee at gmail.com
Fri Mar 20 18:40:14 CDT 2009


Could it be something to do with A.WBS1="88-99999.09" in the second SQL
statement?

I replaced some of your ORs with an IN statement, see if that clears up
anything in the QBE grid.

You might want to replace the "A." with "dbo_LedgerAR" and "Q." with
"qryAR200812"
I used the aliases to make it easier to read on my end.

Also the second statement is missing the Mid([Invoice],4,4) IN
("8888","9999","0999") that is in the first statement's WHERE clause, bot
sure if that was accidental.


SELECT
    A.WBS1,
    A.Invoice,
    A.TransType,

Sum(IIf([account]="261.02",-[amountbillingcurrency],[amountbillingcurrency]))
AS Amount,

IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-Off","Regular"))
AS Type
FROM qryAR200812 Q
    RIGHT JOIN dbo_LedgerAR A
        ON Q.[Project#] = A.WBS1 AND Q.[Invoice#] = A.Invoice
WHERE
    Mid([Invoice],4,4) IN ("8888","9999","0999")
 AND A.Period>[Enter Begin Period?]
 And A.Period<=[Enter End Period?]
 AND Q.[Invoice#] Is Null
GROUP BY
    A.WBS1,
    A.Invoice,
    A.TransType,

IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-Off","Regular")),
Left([wbs1],2)
HAVING
(
A.TransType="CR"
AND
(Sum(IIf([account]="261.02",-[amountbillingcurrency],[amountbillingcurrency])))<>0
AND
(IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-Off","Regular")))="regular"
AND Left([wbs1],2) NOT IN ("RI","SH","TK","UK","AS","IN","MA")
);




SELECT
    A.WBS1,
    A.Invoice,
    A.TransType,

Sum(IIf([account]="261.02",-[amountbillingcurrency],[amountbillingcurrency]))
AS Amount,

IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-Off","Regular"))
AS Type
FROM qryAR200812 Q
    RIGHT JOIN dbo_LedgerAR A
        ON (Q.[Project#] = A.WBS1) AND (Q.[Invoice#] = A.Invoice)
WHERE A.Period>[Enter Begin Period?] And A.Period<=[Enter End Period?] AND
Q.[Invoice#] Is Null
GROUP BY
    A.WBS1,
    A.Invoice,
    A.TransType,

IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-Off","Regular")),
Left([wbs1],2)
HAVING
A.WBS1="88-99999.09"
AND A.TransType="CR"
AND
Sum(IIf([account]="261.02",-[amountbillingcurrency],[amountbillingcurrency]))<>0
AND
IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-Off","Regular"))="regular"
AND Left([wbs1],2) NOT IN ("RI","SH","TK","UK","AS","IN","MA")
;



More information about the AccessD mailing list