IF statement in Access Query

[shepkrm]

Hello!

I am trying to incorporate an if statement into the SQL of my Access query. Each time I try to run the query, I receive the error: "Syntax Error (missing operator) in query expression "IF Inputs.AAH = yes THEN Sum(Select_Date_Inbound_1) ELSE 0"

Here is my SQL:

SELECT 
Select_Date_Inbound_1.Expr1014, 
Sum(Select_Date_Inbound_1.NUM_OF_DOCS) AS SumOfNUM_OF_DOCS, 
IF Inputs.AAH = YES THEN Sum(Select_Date_Inbound_1.BYTES) ELSE 0  AS SumOfBYTES, 
EDIADM_AB_INTERNAL_GS.DIVID, Select_Date_Inbound_1.Expr1013

FROM (Select_Date_Inbound AS Select_Date_Inbound_1 INNER JOIN EDIADM_AB_INTERNAL_GS ON Select_Date_Inbound_1.GS3 = EDIADM_AB_INTERNAL_GS.INTERNAL_GS) INNER JOIN Inputs ON Select_Date_Inbound_1.Expr1014 = Inputs.Year

GROUP BY Select_Date_Inbound_1.Expr1014, EDIADM_AB_INTERNAL_GS.DIVID, Select_Date_Inbound_1.Expr1013, Inputs.AAH

HAVING (((EDIADM_AB_INTERNAL_GS.DIVID)="AAH" Or (EDIADM_AB_INTERNAL_GS.DIVID)="SPD");

Please help if you can or let me know if you need more information.

Thanks so much!
Becky

 

[traingamer]

You need to use iif() syntax

Sum(Select_Date_Inbound_1.NUM_OF_DOCS) AS SumOfNUM_OF_DOCS, [COLOR=red]Sum(Iif(Inputs.AAH = 'YES', [COLOR=blue](Select_Date_Inbound_1.BYTES)[/color],[COLOR=green]0[/color]))[/color]  AS SumOfBYTES,

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[OhioSteve]

Yeah use iif(). It does this:

iif(a,b,c)

a is a logical expression that evaluates to true or false. For example, 1=1, x (if x is a yes/no value), mid(x,1,4)='usa'

b is the value to return if a is true

c is the value to return if a is false

b and c can be any data type and they can be expressions

 

[shepkrm]

Thanks for the help! I can get it to start running, but now it says "The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables"

SELECT 
Select_Date_Inbound_1.Expr1014, 
Sum(Select_Date_Inbound_1.NUM_OF_DOCS) AS SumOfNUM_OF_DOCS, 
Sum(Iif(Inputs.AAH = 'YES', (Select_Date_Inbound_1.BYTES),0))  AS SumOfBYTES, 
EDIADM_AB_INTERNAL_GS.DIVID, Select_Date_Inbound_1.Expr1013

FROM (Select_Date_Inbound AS Select_Date_Inbound_1 INNER JOIN EDIADM_AB_INTERNAL_GS ON Select_Date_Inbound_1.GS3 = EDIADM_AB_INTERNAL_GS.INTERNAL_GS) INNER JOIN Inputs ON Select_Date_Inbound_1.Expr1014 = Inputs.Year

GROUP BY Select_Date_Inbound_1.Expr1014, EDIADM_AB_INTERNAL_GS.DIVID, Select_Date_Inbound_1.Expr1013, Inputs.AAH
HAVING ((EDIADM_AB_INTERNAL_GS.DIVID)="AAH" Or (EDIADM_AB_INTERNAL_GS.DIVID)="SPD");

Any ideas on how I may fix this up?

Thanks again,
Becky