IIF Divide

[Chuck]

I have a report and i'm trying to divide the [PremiumAmt] by 12 if the
[PremiumFreq] equals "Annual". If [PremiumFreq] is not Annual, then it should
just use [PremiumAmt] value. The [PremiumAmt] is a currency field and the
report is based on a query.
I tried this statement in the control source of the text box in the report
but i'm, getting a syntx error.

IIF([PremiumFreq]="Annual",[PremiumAmt]/12,[PremiumAmt])

 

[Allen Browne]

Suggestions:

a) Don't forget the = at the start of the expression.

b) What is the name of this text box? Access gets confused if it has the
same name as a field (such as PremiumAmt), but is bound to something else.
Change its Name property.

c) In some cases, the optimizer in Access is too smart for its own good. It
doesn't bother retrieving all fields if it can't see a text box bound to the
field (or there is sorting/grouping on the field.) To solve this make sure
you have text boxes for PremiumFreq and PremiumAmt on the report (even if
you set their Visible property to No.)

d) It it still fails, perhaps PremiumFreq is a lookup field of type Number,
where a combo box masks the number it really contains. If so, you would need
to match it against the hidden number instead of the text "Annual."

 

[Chuck]

Allen, that's one messed up solution but it worked. I created a text box
[PremiumCalc] and added the two other fields and its working. however, i have
a text box that sums [PremiumAmt] and the value is incorrect because i think
its taking the annual amount instead of the calculated (/12) amount.
the control source is =Sum([PremiumAmt]). I tried using "PremiumCalc" in
the sum but it gives me a parameter box.

Suggestions:

a) Don't forget the = at the start of the expression.

b) What is the name of this text box? Access gets confused if it has the
same name as a field (such as PremiumAmt), but is bound to something else.
Change its Name property.

c) In some cases, the optimizer in Access is too smart for its own good. It
doesn't bother retrieving all fields if it can't see a text box bound to the
field (or there is sorting/grouping on the field.) To solve this make sure
you have text boxes for PremiumFreq and PremiumAmt on the report (even if
you set their Visible property to No.)

d) It it still fails, perhaps PremiumFreq is a lookup field of type Number,
where a combo box masks the number it really contains. If so, you would need
to match it against the hidden number instead of the text "Annual."

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report and i'm trying to divide the [PremiumAmt] by 12 if the
[PremiumFreq] equals "Annual". If [PremiumFreq] is not Annual, then it
should
just use [PremiumAmt] value. The [PremiumAmt] is a currency field and the
report is based on a query.
I tried this statement in the control source of the text box in the report
but i'm, getting a syntx error.

IIF([PremiumFreq]="Annual",[PremiumAmt]/12,[PremiumAmt])

 

[Allen Browne]

Okay, I can't see where you are applying this, so not sure what to suggest
beyond the previous ideas.

 

[Chuck]

I have a text box in the report footer than sums PremiumAmt. The text box is
called SumPremium. It has the =Sum([PremiumAmt]) in the control source but
its not sum'ing correctly. (ie. the number is higher than it should be)

Okay, I can't see where you are applying this, so not sure what to suggest
beyond the previous ideas.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, that's one messed up solution but it worked. I created a text box
[PremiumCalc] and added the two other fields and its working. however, i
have
a text box that sums [PremiumAmt] and the value is incorrect because i
think
its taking the annual amount instead of the calculated (/12) amount.
the control source is =Sum([PremiumAmt]). I tried using "PremiumCalc" in
the sum but it gives me a parameter box.

 

[Allen Browne]

Okay, to the SumPremimum text box in the Report Footer yields a total higher
than you expect.

That happens if records are repeated in the source query. For example, say
you have a table of premiums due, and a table of payments made, so that
someone can pay a premimum in 2 halves. When you create a query that
includes both tables, the premimum shows up in 2 rows (since there were 2
payments.) Consequently when you sum the premimums, it's higher than it
should be.

 

[Chuck]

No i dont think that is the issue. The query is based on one table. Previous
to trying to divide the PremiumAmt by 12 when PremiumFreq = Annual,
everything summed correctly (addition wise but no from a monthly frequency
perspective. Let me illustrate. Note. PremiumFreq is not a visible control on
the report

Previously i would have
PremiumAmt PremiumFreq
817.00 Annual
97.81 Monthly
1161.00 Annual
-------------------------------
2075.81

After using PremiumCalc text control to obtain a monthly PremiumAmt for all
records, i get these results now
PremiumAmt PremiumFreq
68.08 Annual
97.81 Monthly
96.75 Annual
-------------------------------
2075.81

The total is the same, why?

Okay, to the SumPremimum text box in the Report Footer yields a total higher
than you expect.

That happens if records are repeated in the source query. For example, say
you have a table of premiums due, and a table of payments made, so that
someone can pay a premimum in 2 halves. When you create a query that
includes both tables, the premimum shows up in 2 rows (since there were 2
payments.) Consequently when you sum the premimums, it's higher than it
should be.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a text box in the report footer than sums PremiumAmt. The text box
is
called SumPremium. It has the =Sum([PremiumAmt]) in the control source but
its not sum'ing correctly. (ie. the number is higher than it should be)

 

[Allen Browne]

Create a query using this table.

In the query, enter an expression like this in the field row:
PremiumPayt: IIf([PremiumFreq]="Monthly", [PremiumAmt]/12, [PremiumAmt])

Use the query as the Record Source for the report.

Sum the PremiumPayt field instead of the PremiumAmt field.