[AccessD] Fwd: Multiple Criteria Query

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.




More information about the AccessD mailing list