Reply #5: Here is my problem.

[Hawkeye-X]

I'm trying to covert the Excel code below to either
Expression Builder or SQL


Code:

=IF(SUM('CURRENT DMR'!F35)<>0,'CURRENT
DMR'!F35*8.34*'CURRENT DMR'!B35,"") 


Right now, 'CURRENT DMR'!F35 is equal to
[AvgInfluentBOD]![Expr1] and 'CURRENT DMR'!B35 is equal to
[AvgInfluent]![Expr1000]

This doesn't work when I try this code:

30daysBOD:
SELECT
IIf(Sum([AvgInfluentBOD]![Expr1])<>0,[AvgInfluentBOD]![Expr1]*8.34*[AvgInfluent]![Expr1000],"")
AS 30dayBOD;

An error comes out: 

You tried to execute a query that does not include the
specified expression" 'Iif(Not Sum(/)=0,
[AvgInfluentBOD]![Expr1]*8.34*[AvgInfluent]![Expr1000],
"")' as part of an aggregate function.

Some extra helps:

This is from AvgInfluentBOD: 
SELECT Avg([300I (Influent)]![BOD Lab mg/l]) AS Expr1,
Format(Date,"yyyy-mm") AS Expr2 
FROM [300I (Influent)] 
WHERE ((([300I (Influent)].Date) Between [Start Date] And [End
Date])) 
GROUP BY Format(Date,"yyyy-mm"); 

This is from AvgInfluent: 
SELECT Avg([Flow Data Calculations]![Influent Calc]),
Format(Date,"yyyy-mm") 
FROM [Flow Data Calculations] 
WHERE Date Between [Start Date] and [End Date] 
GROUP BY Format(Date,"yyyy-mm"); 

Hope this extra bit of info helps.


So any help would be greatly appreciated!

Hawkeye-X