Keith Williamson
Kwilliamson at RTKL.com
Tue Mar 24 12:04:11 CDT 2009
David, Oddly...I have used the "IN" command in excel msquery....but for some reason thought it was not available in access msquery. Thanks for that tip. OTOH....this still doesn't work. Your code below, includes code after the sql end command ";"......so it won't process. I've added the second criteria, (incorporating the "IN" command) below: ________________________________ SELECT dbo_LedgerAR.WBS1, dbo_LedgerAR.Invoice, dbo_LedgerAR.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 RIGHT JOIN dbo_LedgerAR ON (qryAR200812.[Project#] = dbo_LedgerAR.WBS1) AND (qryAR200812.[Invoice#] = dbo_LedgerAR.Invoice) WHERE (((Mid([Invoice],4,4)) In ("8888","9999","0999")) AND ((dbo_LedgerAR.Period)>[Enter Begin Period?] And (dbo_LedgerAR.Period)<=[Enter End Period?]) AND ((qryAR200812.[Invoice#]) Is Null)) OR (((dbo_LedgerAR.Period)>[Enter Begin Period?] And (dbo_LedgerAR.Period)<=[Enter End Period?]) AND ((qryAR200812.[Invoice#]) Is Null)) GROUP BY dbo_LedgerAR.WBS1, dbo_LedgerAR.Invoice, dbo_LedgerAR.TransType, IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-Off ","Regular")), Left([wbs1],2) HAVING (((dbo_LedgerAR.TransType)="CR") AND ((Sum(IIf([account]="261.02",-[amountbillingcurrency],[amountbillingcurr ency])))<>0) AND ((IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-O ff","Regular")))="regular") AND ((Left([wbs1],2)) Not In ("RI","SH","TK","UK","AS","IN","MA"))) OR (((dbo_LedgerAR.WBS1)="88-99999.09") AND ((dbo_LedgerAR.TransType)="CR") AND ((Sum(IIf([account]="261.02",-[amountbillingcurrency],[amountbillingcurr ency])))<>0) AND ((IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-O ff","Regular")))="regular") AND ((Left([wbs1],2)) Not In ("RI","SH","TK","UK","AS","IN","MA"))); _______________________________________ Oddly...I can't make heads or tales for why some of the data is returned. Most don't fit the criteria at all. Grrrr! In the latest period, I am running this query...I should only get one data line returned. The first line of criteria should yield no records....and the second line should yield (1). But, I am getting 362 records returned. I am getting a number of lines with null values for dbo_Ledger.Invoice. On the first criteria...I am specifically filtering for: (((Mid([Invoice],4,4)) In ("8888","9999","0999"))...so there should be no "null" invoices. The second criteria......in no case in the database is there a null invoice associated with the WBS1="88-99999.09'. So...I can't figure out how I get back 91 (out of 362 records returned) with null value for invoice. ???? Regards, Keith Williamson | Associate, Asst. Controller | kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond St. | Baltimore, MD 21231 410.537.6098 Direct | 410.276.4232 Fax | www.rtkl.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith E. Williamson Sent: Sunday, March 22, 2009 5:15 PM To: 'Access Developers discussion and problem solving' Subject: Re: [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. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -------------------------------------------------------------------------------- The information contained in this communication is confidential, may be privileged and is intended for the exclusive use of the above named addressee(s). If you are not the intended recipient(s), you are expressly prohibited from copying, distributing, disseminating, or in any other way using any of the information contained within this communication. If you have received this communication in error, please contact the sender by telephone at (410) 537-6000 or by response via e-mail and permanently delete the original email and any copies.