Link to home
Start Free TrialLog in
Avatar of APD Toronto
APD TorontoFlag for Canada

asked on

Wrong Number of Arguments

Hello Experts,

Can anyone see why i'm getting Wrong Number of Arguments ?  I have been at this 1h.

SELECT [tblLocs].[fldLocation]  AS HOTEL,  
[tblAgents].[fldLName] & ', ' & [tblAgents].[fldFName] AS AGENT, 

SUM(
	IIF((fldProductID = 'CSST1' OR fldProductID = 'CSST2' OR fldProductID = 'COMBO1' OR fldProductID = 'COMBO2') 
	AND ((fldPickupDate BETWEEN #2012-10-07# AND #2012-11-06# ) )), 
((IIf([fldFamQty]>0,[fldFamQty],0) * 4) +IIf([fldAdultQty]>0,[fldAdultQty],0) +IIf([fldSenQty]>0,[fldSenQty],0) +IIf([fldStudQty]>0,[fldStudQty],0) +IIf([fldChildQty]>0,[fldChildQty],0) ) , 0) AS CSSTPass1, 0 AS CSSTPass2 

FROM [tblReservations] AS tblRes, [tblLocations] AS tblLocs, tblAgents 

GROUP BY HOTEL, AGENT  

HAVING (tblReservations.fldArchived = False) AND (fldTripType <> 'E')  AND (fldAgent <> 1)  


AND ([tblRes].fldBookLoc = [tblLocs].fldLocID)  AND ([tblRes].fldAgent = [tblAgents].fldAgentID) 

Open in new window


Thanks
Avatar of appari
appari
Flag of India image

i suppose there are mismatched closing paranthesis in iif statements.

try this
SELECT [tblLocs].[fldLocation]  AS HOTEL,  
[tblAgents].[fldLName] + ', ' + [tblAgents].[fldFName] AS AGENT, 

SUM(
	IIF(
		(fldProductID = 'CSST1' OR fldProductID = 'CSST2' 
			OR fldProductID = 'COMBO1' OR fldProductID = 'COMBO2') 
			AND ((fldPickupDate BETWEEN #2012-10-07# AND #2012-11-06# ) )
		, 
((IIf([fldFamQty]>0,[fldFamQty],0) * 4) +
IIf([fldAdultQty]>0,[fldAdultQty],0) +
IIf([fldSenQty]>0,[fldSenQty],0) +
IIf([fldStudQty]>0,[fldStudQty],0) +
IIf([fldChildQty]>0,[fldChildQty],0) ) , 0)) AS CSSTPass1, 0 AS CSSTPass2 

FROM [tblReservations] AS tblRes, [tblLocations] AS tblLocs, tblAgents 

GROUP BY HOTEL, AGENT  

HAVING (tblReservations.fldArchived = False) AND (fldTripType <> 'E')  AND (fldAgent <> 1)  


AND ([tblRes].fldBookLoc = [tblLocs].fldLocID)  AND ([tblRes].fldAgent = [tblAgents].fldAgentID) 

Open in new window

Avatar of IrogSinta
This whole section is incorrect with regards to your parentheses; however, I'm not certain what you're trying to do with this part so I can't give you the correct syntax.
SUM(
	IIF((fldProductID = 'CSST1' OR fldProductID = 'CSST2' OR fldProductID = 'COMBO1' OR fldProductID = 'COMBO2') 
	AND ((fldPickupDate BETWEEN #2012-10-07# AND #2012-11-06# ) )), 
((IIf([fldFamQty]>0,[fldFamQty],0) * 4) +IIf([fldAdultQty]>0,[fldAdultQty],0) +IIf([fldSenQty]>0,[fldSenQty],0) +IIf([fldStudQty]>0,[fldStudQty],0) +IIf([fldChildQty]>0,[fldChildQty],0) ) , 0) AS CSSTPass1, 0 AS CSSTPass2 

Open in new window

I simplified the SUM() expression, balanced the parentheses and renamed the HAVING clause into the WHERE clause.  I think the Group By needs to see the raw fields instead of the aliased fields/expressions.

SELECT [tblLocs].[fldLocation]  AS HOTEL,  
[tblAgents].[fldLName] & ', ' & [tblAgents].[fldFName] AS AGENT, 
SUM(
	IIF(fldProductID In ('CSST1' , 'CSST2' , 'COMBO1' , 'COMBO2') 
	AND (fldPickupDate BETWEEN #2012-10-07# AND #2012-11-06# ), 
IIf([fldFamQty]>0, [fldFamQty], 0) * 4 + 
IIf([fldAdultQty]>0, [fldAdultQty], 0) +
IIf([fldSenQty]>0,[fldSenQty], 0) + 
IIf([fldStudQty]>0,[fldStudQty], 0) + 
IIf([fldChildQty]>0,[fldChildQty],0)  , 0) ) AS CSSTPass1
, 0 AS CSSTPass2 

FROM [tblReservations] AS tblRes, [tblLocations] AS tblLocs, tblAgents 

WHERE (tblReservations.fldArchived = False) 
AND (fldTripType <> 'E')  
AND (fldAgent <> 1)  
AND ([tblRes].fldBookLoc = [tblLocs].fldLocID)  
AND ([tblRes].fldAgent = [tblAgents].fldAgentID) 

GROUP BY [tblLocs].[fldLocation], [tblAgents].[fldLName] & ', ' & [tblAgents].[fldFName]

Open in new window


You might also consider incorporating the table joining expressions in the WHERE clause up into the FROM clause, adding JOIN keywords and parentheses.
In addition to the advice above, I find it easier to understand expressions using the Switch function than embedded IIf expressions:

https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.html
Avatar of APD Toronto

ASKER

aikmark, your solution worked, but what did you change?

i thought when you GROUP BY, you cannot use WHERE - guess not.

Matt, how would i use the Switch in my case - i never used it, but i have a lot more calculations to do
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial