Expected value "not what I expected"

[GMFTM]

Good Morning All,

Here is what I am doing:

IIf((([Auction Data].[Sale_Price])<([211 Offered Sold StDev MY].[SPStDevMin])),([Auction Data] [Sale_Price])) Or IIf((([Auction Data].[Sale_Price])>([211 Offered Sold StDev MY].[SPStDevMax])),([Auction Data].[Sale_Price])) AS [SP Exp],

IIf((([Auction Data].[Days to Sell])<([211 Offered Sold StDev MY].[DTSStDevMin])),([Auction Data].[Days to Sell])) Or IIf((([Auction Data].[Days to Sell])>([211 Offered Sold StDev MY].[DTSStDevMax])),([Auction Data].[Days to Sell])) AS [DTS Exp]

Now my result is a -1 where it is out of range which if fine, but how I wrote this I was expecting the actual value from the field, either the sale_price in the column or the other would be the days to sell.

Any help would be appreciated.

T

 

[Joe4]

I don't think you have structured your IF formulas correctly. Any "OR" clause would typically be found in the first argument of the IIF function, not in the second or third, nor outside of it.

It should be structured like this:
=IIf((condition1) or (condition2),what to return if either condition is true, what to return if both conditions are false)

 

[GMFTM]

Agreed, I figured that out finally, lol. Now I have a seperate issue:

Sale Rate: IIF(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered])<([211 Offered Sold StDev MY].[SRStDevMin})),(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered]))

I am getting a syntax error and going crossed eyed looking at it? Help

I don't think you have structured your IF formulas correctly. Any "OR" clause would typically be found in the first argument of the IIF function, not in the second or third, nor outside of it.

It should be structured like this:
=IIf((condition1) or (condition2),what to return if either condition is true, what to return if both conditions are false)

 

[AliGW]

Maybe because of this:

Sale Rate: IIF(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered])<([211 Offered Sold StDev MY].[SRStDevMin})),(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered]))

 

[GMFTM]

Ahh, I did goof there, but that is still not it..ugh

Sale Rate: IIF(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered])<([211 Offered Sold StDev MY].[SRStDevMin])),(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered]))

Getting: "The expression you entered has a function containing the wrong number of arguements"

Maybe because of this:

Sale Rate: IIF(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered])<([211 Offered Sold StDev MY].[SRStDevMin})),(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered]))

 

[Joe4]

How/where exactly are you doing this?
If in a query, you typically need to have it be an Aggregate (Totals) Query in order to use aggregate functions like SUM and COUNT.
If it is in query, switch the query to SQL View and Copy and Paste the code here.

 

[GMFTM]

How/where exactly are you doing this?
If in a query, you typically need to have it be an Aggregate (Totals) Query in order to use aggregate functions like SUM and COUNT.
If it is in query, switch the query to SQL View and Copy and Paste the code here.

SELECT [Auction Data].AuctionName,
[Auction Data].Company,
[Auction Data].Month,
[Auction Data].MY,
Count([Auction Data].Offered) AS CountOfOffered,
IIf(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered])<([211 Offered Sold StDev MY].[SRStDevMin]),(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered]))) AS [Sale Rate],
IIf(([Auction Data].[Sale_Price])<([211 Offered Sold StDev MY].[SPStDevMin]),([Auction Data].[Sale_Price]),
IIf((([Auction Data].[Sale_Price])>([211 Offered Sold StDev MY].[SPStDevMax])),([Auction Data].[Sale_Price]))) AS [Sale Price], IIf(([Auction Data].[Days to Sell])<([211 Offered Sold StDev MY].[DTSStDevMin]),([Auction Data].[Days to Sell]),IIf((([Auction Data].[Days to Sell])>([211 Offered Sold StDev MY].[DTSStDevMax])),([Auction Data].[Days to Sell]))) AS [Days to Sell]

FROM [Auction Data]
LEFT JOIN [211 Offered Sold StDev MY] ON ([Auction Data].MY = [211 Offered Sold StDev MY].MY)
AND ([Auction Data].Month = [211 Offered Sold StDev MY].Month)
AND ([Auction Data].Company = [211 Offered Sold StDev MY].Company)

GROUP BY [Auction Data].AuctionName,
[Auction Data].Company,
[Auction Data].Month,
[Auction Data].MY,
IIf(([Auction Data].[Sale_Price])<([211 Offered Sold StDev MY].[SPStDevMin]),([Auction Data].[Sale_Price]),
IIf((([Auction Data].[Sale_Price])>([211 Offered Sold StDev MY].[SPStDevMax])),([Auction Data].[Sale_Price]))),
IIf(([Auction Data].[Days to Sell])<([211 Offered Sold StDev MY].[DTSStDevMin]),([Auction Data].[Days to Sell]),
IIf((([Auction Data].[Days to Sell])>([211 Offered Sold StDev MY].[DTSStDevMax])),([Auction Data].[Days to Sell])))

HAVING (((IIf(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered])<([211 Offered Sold StDev MY].[SRStDevMin]),(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered]))))<>0));

 

[Joe4]

I think you have a right parenthesis in the wrong spot.
Instead of this:

[COLOR=#333333]Sale Rate: IIF(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered])<([211 Offered Sold StDev MY].[SRStDevMin][/COLOR][COLOR=#ff0000]))[/COLOR][COLOR=#333333],(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered][/COLOR][COLOR=#ff0000]))[/COLOR][COLOR=#333333][/COLOR]

I think it should be:

[COLOR=#333333]Sale Rate: IIF(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered])<([211 Offered Sold StDev MY].[SRStDevMin][/COLOR][COLOR=#ff0000])[/COLOR][COLOR=#333333],(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered][/COLOR][COLOR=#ff0000])))[/COLOR][COLOR=#333333][/COLOR]

 

[GMFTM]

Tried that, I see your line of thinking here, TY. However, it is giving me the query does not include expression "......" as part of an aggregate function. I really cannot figure out the way to build this to get past it. I have tried Avg so that it would aggregate buy no bueno.....

I think you have a right parenthesis in the wrong spot.
Instead of this:

[COLOR=#333333]Sale Rate: IIF(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered])<([211 Offered Sold StDev MY].[SRStDevMin][/COLOR][COLOR=#ff0000]))[/COLOR][COLOR=#333333],(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered][/COLOR][COLOR=#ff0000]))[/COLOR]

I think it should be:

[COLOR=#333333]Sale Rate: IIF(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered])<([211 Offered Sold StDev MY].[SRStDevMin][/COLOR][COLOR=#ff0000])[/COLOR][COLOR=#333333],(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered][/COLOR][COLOR=#ff0000])))[/COLOR]

 

[Joe4]

What happened when you tried Average?

Sometimes, it is easier to break it up into multiple queries instead of trying to do too much in one single query, especially if you are trying to aggregate different things in a single query (like a Sum and Count of different fields).