proper syntex for equation

  • Thread starter Thread starter Bert via AccessMonster.com
  • Start date Start date
B

Bert via AccessMonster.com

I have tried to enter this type of equation and I get error messages when I
generate my ACCESS report. This is in the footer of the report.

THE FIELD "DOLLARS SAVED" IS: AUTHORITY - INDEMNITY PAID = , IF
INDEMNITY PAID IS "0" THAN "DOLLARS SAVED" IS "0".

It works fine when there is an amount in the indemnity paid field, but if the
indemnity paid is "0" cause nothing was paid, then there were no dollars
saved.

Please help. Thank you for your assistance.

Roberta
 
THE FIELD "DOLLARS SAVED" IS: AUTHORITY - INDEMNITY PAID = , IF
INDEMNITY PAID IS "0" THAN "DOLLARS SAVED" IS "0".

is the above your actual expression? if not, please post the actual
expression (copy/paste). if yes, where is this expression - in the
ControlSource of an unbound textbox? in the report's VBA module? in a query
underlying the report?
 
Thank you for replying Tina. Below is the expression. It is located in the
ControlSource. It is not a VBA module (not skilled to that level). A query
underlies the report, however the expression is not part of the query.

=Sum([Settlement Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT]), IIf ([INDEMNITY_PAID_AMT], "0", "0")

THE FIELD "DOLLARS SAVED" IS: AUTHORITY - INDEMNITY PAID = , IF
INDEMNITY PAID IS "0" THAN "DOLLARS SAVED" IS "0".

is the above your actual expression? if not, please post the actual
expression (copy/paste). if yes, where is this expression - in the
ControlSource of an unbound textbox? in the report's VBA module? in a query
underlying the report?
I have tried to enter this type of equation and I get error messages when I
generate my ACCESS report. This is in the footer of the report.
Click to expand...
[quoted text clipped - 9 lines]
Roberta
Click to expand...
Click to expand...
 
Additonal thoughts: OK I tried this:
=Sum([Settlement Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT])=IIf([2RunAuthority06]![INDEMNITY_PAID_AMT]=0,"No Indem
Paid")

This is the goal, that if there is a zero amount, then "dollars saved" will
return zero because nothing was paid.

**************************************
Thank you for replying Tina. Below is the expression. It is located in the
ControlSource. It is not a VBA module (not skilled to that level). A query
underlies the report, however the expression is not part of the query.

=Sum([Settlement Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT]), IIf ([INDEMNITY_PAID_AMT], "0", "0")
THE FIELD "DOLLARS SAVED" IS: AUTHORITY - INDEMNITY PAID = , IF
INDEMNITY PAID IS "0" THAN "DOLLARS SAVED" IS "0".
Click to expand...
[quoted text clipped - 9 lines]
Click to expand...
 
okay, i think you're close, just haven't quite got the concept of the IIf()
function. try this

==IIf(Nz([2RunAuthority06]![INDEMNITY_PAID_AMT], 0)=0, 0, Sum([Settlement
Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT]))

i stuck the Nz() function in there *just in case* the [INDEMNITY_PAID_AMT]
is ever Null, rather than showing zero or another number.

for more details you can read up on the IIf() and Nz() functions in Access
Help.

hth


Bert via AccessMonster.com said:
Additonal thoughts: OK I tried this:
=Sum([Settlement Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT])=IIf([2RunAuthority06]![INDEMNITY_PAID_AMT]=0,"No
Click to expand...
Indem
Paid")

This is the goal, that if there is a zero amount, then "dollars saved" will
return zero because nothing was paid.

**************************************
Thank you for replying Tina. Below is the expression. It is located in the
ControlSource. It is not a VBA module (not skilled to that level). A query
underlies the report, however the expression is not part of the query.

=Sum([Settlement Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT]), IIf ([INDEMNITY_PAID_AMT], "0", "0")
THE FIELD "DOLLARS SAVED" IS: AUTHORITY - INDEMNITY PAID = , IF
INDEMNITY PAID IS "0" THAN "DOLLARS SAVED" IS "0".
Click to expand...
[quoted text clipped - 9 lines]
Roberta
Click to expand...
Click to expand...
Click to expand...
 
Good sleuthing tina.

One small correction is to use only a single "=" rather than two.

--
Duane Hookom
MS Access MVP
--

tina said:
okay, i think you're close, just haven't quite got the concept of the
IIf()
function. try this

==IIf(Nz([2RunAuthority06]![INDEMNITY_PAID_AMT], 0)=0, 0, Sum([Settlement
Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT]))

i stuck the Nz() function in there *just in case* the [INDEMNITY_PAID_AMT]
is ever Null, rather than showing zero or another number.

for more details you can read up on the IIf() and Nz() functions in Access
Help.

hth


Bert via AccessMonster.com said:
Additonal thoughts: OK I tried this:
=Sum([Settlement Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT])=IIf([2RunAuthority06]![INDEMNITY_PAID_AMT]=0,"No
Click to expand...
Indem
Paid")

This is the goal, that if there is a zero amount, then "dollars saved" will
return zero because nothing was paid.

**************************************
Thank you for replying Tina. Below is the expression. It is located in the
ControlSource. It is not a VBA module (not skilled to that level). A query
underlies the report, however the expression is not part of the query.

=Sum([Settlement Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT]), IIf ([INDEMNITY_PAID_AMT], "0", "0")

THE FIELD "DOLLARS SAVED" IS: AUTHORITY - INDEMNITY PAID = , IF
INDEMNITY PAID IS "0" THAN "DOLLARS SAVED" IS "0".
[quoted text clipped - 9 lines]

Roberta
Click to expand...
Click to expand...
Click to expand...
 
oops!! cut and paste typo. good eyes, Duane - i didn't notice that the equal
sign was really looong! :)
 
oops, slight error in my expression, Bert. i used two "=" signs
accidentally. expression should be

=IIf(Nz([2RunAuthority06]![INDEMNITY_PAID_AMT], 0)=0, 0, Sum([Settlement
Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT]))

the above will be all one line in the control's RecordSource, of course.

hth


Bert via AccessMonster.com said:
Additonal thoughts: OK I tried this:
=Sum([Settlement Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT])=IIf([2RunAuthority06]![INDEMNITY_PAID_AMT]=0,"No
Click to expand...
Indem
Paid")

This is the goal, that if there is a zero amount, then "dollars saved" will
return zero because nothing was paid.

**************************************
Thank you for replying Tina. Below is the expression. It is located in the
ControlSource. It is not a VBA module (not skilled to that level). A query
underlies the report, however the expression is not part of the query.

=Sum([Settlement Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT]), IIf ([INDEMNITY_PAID_AMT], "0", "0")
THE FIELD "DOLLARS SAVED" IS: AUTHORITY - INDEMNITY PAID = , IF
INDEMNITY PAID IS "0" THAN "DOLLARS SAVED" IS "0".
Click to expand...
[quoted text clipped - 9 lines]
Roberta
Click to expand...
Click to expand...
Click to expand...
 
Tina your help is appreciated. An error was still present. I knew it was a
matter of the proper order of the expression:

=IIF(Sum([2RunAuthority06]![INDEMNITY_PAID_AMT])=0,0,(Sum([2RunAuthority06]!
[Settlement Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT]))

Another friend of mine saw the minor difference in 0=0 part of the expression.
He made the changes. It worked.

Thank you so much as you help narrow down the proper expression.
oops, slight error in my expression, Bert. i used two "=" signs
accidentally. expression should be

=IIf(Nz([2RunAuthority06]![INDEMNITY_PAID_AMT], 0)=0, 0, Sum([Settlement
Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT]))

the above will be all one line in the control's RecordSource, of course.

hth
Additonal thoughts: OK I tried this:
=Sum([Settlement Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
Click to expand...
[quoted text clipped - 18 lines]
Click to expand...
 
you're welcome :)


Bert via AccessMonster.com said:
Tina your help is appreciated. An error was still present. I knew it was a
matter of the proper order of the expression:

=IIF(Sum([2RunAuthority06]![INDEMNITY_PAID_AMT])=0,0,(Sum([2RunAuthority06]!
[Settlement Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT]))

Another friend of mine saw the minor difference in 0=0 part of the expression.
He made the changes. It worked.

Thank you so much as you help narrow down the proper expression.
oops, slight error in my expression, Bert. i used two "=" signs
accidentally. expression should be

=IIf(Nz([2RunAuthority06]![INDEMNITY_PAID_AMT], 0)=0, 0, Sum([Settlement
Authority_Regional Office_Officer])-Sum([2RunAuthority06]!
[INDEMNITY_PAID_AMT]))

the above will be all one line in the control's RecordSource, of course.

hth
Additonal thoughts: OK I tried this:
=Sum([Settlement Authority_Regional
Click to expand...
Office_Officer])-Sum([2RunAuthority06]!
[quoted text clipped - 18 lines]
Roberta
Click to expand...
Click to expand...
Click to expand...
 
Back
Top