Re: Problems with an SQL

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Design the function so it accepts the AcctMgrID, and returns True or False:

Function getmyvariable(varAcctMgrID As Variant) As Boolean
getmyvariable = ((IsNull(varAcctMgrID) OR _
(varAcctMgrID = SomeVariable) OR (SomeVariable = 99))
End If

Then use it in the WHERE clause like this:
WHERE getmyvariable(tblAcctMgr.AcctMgrID)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"tsison7" <tsison7@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9DD05DCB-D3CA-4175-B20A-3F8AD79B3CFB@xxxxxxxxxxxxxxxx
I have a report which draws from the query below (simplified). Basically, I
want it to either filter for all customers - where getmyvariable() = 99 - or
for a specific account manager only. This is determined by which command
button calls on the report which correspondingly sets the getmyvariable()
function to either 99 for all or for the specific account manager.

My problem is the query below will only work when it is called for a
specific account manager. I even tried putting the individual conditions in
to check if they are correct and they work (ie. [tblAcctMgr].[AcctMgrID]<>99)
by itself generates the correct results.

Your hellp is appreciated....

SELECT qrySCS.[Cust ID], tblCustomers.Customer, Sum(qrySCS.Qty) AS SumOfQty
FROM tblAcctMgr INNER JOIN (qrySCS INNER JOIN tblCustomers ON qrySCS.[Cust
ID] = tblCustomers.PSCustNo) ON tblAcctMgr.AcctMgrID = tblCustomers.AcctMgrID
WHERE
(((tblAcctMgr.AcctMgrID)=IIf(getmyvariable()=99,[tblAcctMgr].[AcctMgrID]<>99,getmyvariable())))
GROUP BY qrySCS.[Cust ID], tblCustomers.Customer;
--
TIA

.

Quantcast