Problems with an SQL

[tsison7]

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;

 

[Allen Browne]

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)

 

[John Spencer]

I would try the following.

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=getmyvariable()
OR getmyvariable() = 99
GROUP BY qrySCS.[Cust ID], tblCustomers.Customer;

Although if the query you have shown is simplified and the criteria in
the where clause are much more complex this could be too complex to execute.


--

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================