Conditional expression in Query

[sal]

I am trying to use criteria to create a conditional expression in an Access Query.

IIf([STHD DIP]=”Skokomish”, Round([Redds]*0.7175*2,0), IIf([STHD DIP]=”West Hood Canal”, Round([Redds]*0.7175*2,0), Round([Redds]*0.81*2,0)))

I have a nested IIf expression to evaluate a field where two values would be calculated differently than the rest. However, the syntax is wrong. The error is coming up on the value, "West Hood Canal". Any help on this would be greatly appreciated.

 

[plog]

Can you explain the error better? Perhaps with sample values?

Are you getting an error or just unexpected values? What are those values that you are feeding in and getting out?

 

[sal]

Yes, It's an aggregate function drawn from three related tables. The calculation works without conditional statements. I uploaded a screenshot of the query in case that helps illustrate the structure and issue. Thanks very much.

 

[plog]

You didn't explain the issue--error message or unexpected results?

If it's an aggregate query, you need to assign an aggregate function to the code. I don't think having it as an Expression and not using an aggregate function (MIN, MAX, SUM, etc.) in the top part will work. Check out your 'Live' field as an example.

With that said your expression doesn't need to be a nested IIF statement:

IIf([STHD DIP]=”Skokomish”, Round([Redds]*0.7175*2,0), IIf([STHD DIP]=”West Hood Canal”, Round([Redds]*0.7175*2,0), Round([Redds]*0.81*2,0)))

For simplicity's sake let's use letters for the criteria and numbers for the outputs. That means your code is this:

IIF(A, 1, IIF(B, 1, 2))

Since A and B yield the same output, that expression can be simplified and unnested with an OR like so:

IIF(A OR B, 1, 2)

I would structure the expression like that and make it simpler and see if that helps.

 

[sal]

Yes, I did try that originally:

Esc:IIf([STHD DIP]=”Skokomish” OR [STHD DIP]=”West Hood Canal”, Round([Redds]*0.7175*2,0), Round([Redds]*0.81*2,0))

But also got an invalid syntax error. That's why I tried nesting my IIf statements. None of my statements have returned any results. Each time it has been a syntax error.

If I simply insert a single calculation, Esc: Round([Redds]*0.81*2,0), it works fine. So I assume it is not a problem with the query structure itself.

 

[plog]

Does every record coming through to the query have a numeric value in the [Redds] field? Sure there aren't any nulls? I see more than 1 table in this query--did you use an INNER or OUTER JOIN to connect them? That could cause nulls.

Can you post your database?

 

[sal]

The Redds field is a calculated field from one table, which is summed then multiplied in an expression to get Esc (see shapsot). It works fine as pictured, but when I introduce the conditions, which do come from one of the other related tables, it does not work.

The db is pretty big. Here is the sql version of the query that works:

SELECT [WRIA] & "." & [StreamCode] AS Code, [Survey info].Water, Sum(Nz([Live_Adult_F_Count])+Nz([Live_Adult_Unk_Count])+Nz([Live_Adult_M_Count])+Nz([M_MarkUKL])+Nz([M_NMUKL])+Nz([F_MarkUKL])+Nz([F_NMUKL])+Nz([U_MarkUKL])+Nz([U_NMUKL])) AS Live, Sum(Nz([New_Redd_Comp])+Nz([New_Redd_Active])) AS Redds, Round([Redds]*0.81*2,0) AS Esc
FROM [Survey waters] INNER JOIN ([Survey info] INNER JOIN [SGS Data] ON [Survey info].SurveyID = [SGS Data].SurveyID) ON [Survey waters].StreamName = [Survey info].Water
GROUP BY [WRIA] & "." & [StreamCode], [Survey info].Water, [Survey waters].District, [SGS Data].RunYear, [SGS Data].Species
HAVING ((([Survey waters].District)=15) AND (([SGS Data].RunYear)=2016) AND (([SGS Data].Species)=6))
ORDER BY [WRIA] & "." & [StreamCode];

 

[Galaxiom]

You need to take a step back because you are putting far too much data hard coded into the query.

These factors you are multiplying belong in a related table so the required factor is available directly to the query without conditional statements.

All those values you are adding together in the same record also belong in a related table where they can be summed in an aggregate.

These changes will result in a far simpler query that can be reconfigured using records in tables.

 

[CJ_London]

not sure if it is the way the code has presented itself when pasted to the post but looks like you are using ”rather than " - which are used correctly here

.....WRIA] & "." & [StreamCode].....

but not here

Esc:IIf([STHD DIP]=”Skokomish” OR [STHD DIP]=”West Hood Canal”, Round([Redds]*0.7175*2,0), Round([Redds]*0.81*2,0))

 

[sal]

By storing the conversion factor in the table with water attributes and adding that to the query I was able to produce the desired result. I thought it could be done on the fly, but this method works. Thanks.