[AccessD] Fwd: Multiple Criteria Query

Thanks a lot, David. 

I'll try that, when I get back into the office, tomorrow. I appreciate
your time, on this. 

BTW...the ---Mid([Invoice],4,4) IN 

("8888","9999","0999")--- Was left out on purpose...as I am looking for
ALL invoices in the second select statement. 

Thanks, 

Keith W. 

-----Original Message----- 

From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee 

Sent: Friday, March 20, 2009 7:40 PM 

To: Access Developers discussion and problem solving 

Subject: [AccessD] Fwd: Multiple Criteria Query 

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],[amountbillingcurren
cy])) 

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],[amountbillingcurre
ncy])))<>0 

AND 

(IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-Of
f","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],[amountbillingcurren
cy])) 

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],[amountbillingcurren
cy]))<>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") 

; 

-- 

AccessD mailing list 

AccessD at databaseadvisors.com 

http://databaseadvisors.com/mailman/listinfo/accessd 

Website: http://www.databaseadvisors.com 


  _____  

I am using the Free version of SPAMfighter
<http://www.spamfighter.com/len> .
We are a community of 6 million users fighting spam.
SPAMfighter has removed 398 of my spam emails to date.
The Professional version does not have this message.