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.