Need help with some code

[GMFTM]

I am working with dates that I I have *** a numeric value. I am wanting to get a sum of values between two date ranges and divide that by another sum between to date ranges to get a value, that is ultimately a percentage.

Here is how I wrote it, but getting an error:

80Percent: IIF([YM] Beteen "201609" And "201701"),Sum(CountOfVIN))/IIF([YM] Beteen "201609" And "201703'),Sum(CountOfVIN))

 

[Norie]

What data type is the field [YM]?

 

[GMFTM]

Numeric

YM: Year([Sale_Date])*100+Month([Sale_Date])

What data type is the field [YM]?

 

[Joe4]

Two things I see:

1. A typo. The word in "between", not "beteen"

2. Your calculation is a number field. Do not compare it to text (anything enclosed in double-quotes is treated as literal text), i.e.
should be 201609 and 201701, with no double quotes around them.

Also, where exactly are you trying to perform this calculation? In a query, form, report?

 

[GMFTM]

I did see that and corrected it, lol "Beteen"

I am performing this in a query.

80Percent: IIf(([YM]) Between 201609 And 201701,Sum([CountOfVIN])/IIf(([YM]) Between 201609 And 201703,Sum([CountOfVIN]),Null))

This is where I am now, however I am getting "Your query does not include the specified expression 'IIf(([YM]) Between 201609 And 201701,Sum([CountOfVIN])/IIf(([YM]) Between 201609 And 201703,Sum([CountOfVIN]),Null))' as part of an aggregate function.

I have 4 other fields in this query so I am using "Group By" for them and "Expression" for the calculation.

Two things I see:

1. A typo. The word in "between", not "beteen"

2. Your calculation is a number field. Do not compare it to text (anything enclosed in double-quotes is treated as literal text), i.e.
should be 201609 and 201701, with no double quotes around them.

Also, where exactly are you trying to perform this calculation? In a query, form, report?

 

[Joe4]

Please post the SQL code for your query.

 

[GMFTM]

SELECT [6 months view 80 percent].Region, [6 months view 80 percent].RSM, [6 months view 80 percent].DRM, [6 months view 80 percent].Dlr_Name, Avg(IIf([YM] Between 201609 And 201701,([CountOfVIN])/IIf([YM] Between 201609 And 201703,([CountOfVIN]),Null))) AS 80Percent
FROM [6 months view 80 percent]
GROUP BY [6 months view 80 percent].Region, [6 months view 80 percent].RSM, [6 months view 80 percent].DRM, [6 months view 80 percent].Dlr_Name;

Please post the SQL code for your query.

 

[Joe4]

OK, so you are using an Aggregate Query. That is good. However, instead of having the calculation you posted, just pick the field you want to Average, and select "Average" on the Totals line for that field (instead of "Group By"), and put your "YM" criteria in the WHERE clause of your query (add the "YM", but uncheck the "Show" box, and enter the Criteria for it on the Criteria line).

 

[GMFTM]

Not sure I follow? I am trying to take the sum of a count [VIN] and divide it by another sum of a count [VIN]. For the first part I only want it to sum between the two date ranges and then on the second part "divide by" a different date range to sum.

OK, so you are using an Aggregate Query. That is good. However, instead of having the calculation you posted, just pick the field you want to Average, and select "Average" on the Totals line for that field (instead of "Group By"), and put your "YM" criteria in the WHERE clause of your query (add the "YM", but uncheck the "Show" box, and enter the Criteria for it on the Criteria line).

 

[Joe4]

I am trying to take the sum of a count [VIN] and divide it by another sum of a count [VIN]. For the first part I only want it to sum between the two date ranges and then on the second part "divide by" a different date range to sum

So, you are trying to do your own division, and then take the Average of that?

[COLOR=#333333][B]Avg[/B](IIf([YM] Between 201609 And 201701,([CountOfVIN])/IIf([YM] Between 201609 And 201703,([CountOfVIN]),Null))) AS 80Percent[/COLOR]

Are YM and CountOfVin part of [6 months view 80 percent]?

Also, I don't think you have structured your IIF statement correctly. You have it so it could return a NULL in your division, and you cannot divide by NULL.