calculated field ignoring IIF statement

  • Thread starter Thread starter spence
  • Start date Start date
S

spence

I have a subform calculated field [Amount] that looks like
this:

=IIf([SC]="731" Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.1175)

It works fine, but I have a calculated form in the footer
of the subform that is supposed to sum the line items in
field [Amount]:

=Sum([Amount])

The sum it gives me ignores the multiplier 1.1175 in all
cases even though the [Amount] line items display the
correctly multiplied amount.

What am I doing wrong here?

thanks,
spence
 
I have a subform calculated field [Amount] that looks like
this:

=IIf([SC]="731" Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.1175)

It works fine, but I have a calculated form in the footer
of the subform that is supposed to sum the line items in
field [Amount]:

=Sum([Amount])

The sum it gives me ignores the multiplier 1.1175 in all
cases even though the [Amount] line items display the
correctly multiplied amount.

What am I doing wrong here?
Click to expand...

Since [Amount] is a calculated field just sum the calculation:

=Sum(IIf([SC]="731" Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.1175))

- Jim
 
Thanks, Jim. Although it's a little OT, I'd like to ask you about the
related problem I'm having with this calculation. I need to make the
same calculation [Amount] in a Query, so I used:

Amount: IIf([SC]="731" Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.1175)

It returns #Error in all records for the field [Amount] except those
where [SC]="731". I don't understand it at all. I posted this problem on
the Query group but no one seemed to know the solution. Maybe you
have some insight?

Thanks again,
spence

-----Original Message-----
I have a subform calculated field [Amount] that looks like
this:

=IIf([SC]="731" Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.1175)

It works fine, but I have a calculated form in the footer
of the subform that is supposed to sum the line items in
field [Amount]:

=Sum([Amount])

The sum it gives me ignores the multiplier 1.1175 in all
cases even though the [Amount] line items display the
correctly multiplied amount.

What am I doing wrong here?
Click to expand...

Since [Amount] is a calculated field just sum the calculation:

=Sum(IIf([SC]="731" Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.1175))

- Jim

.
Click to expand...
 
Actually, now that I've tested your suggestion for the
form, it doesn't work either. It also returns a #Error in
the field. There must be a connection between the behavior
of the field in the form and in the query, but it seems
pretty straight forward to me.

-----Original Message-----
I have a subform calculated field [Amount] that looks like
this:

=IIf([SC]="731" Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.1175)

It works fine, but I have a calculated form in the footer
of the subform that is supposed to sum the line items in
field [Amount]:

=Sum([Amount])

The sum it gives me ignores the multiplier 1.1175 in all
cases even though the [Amount] line items display the
correctly multiplied amount.

What am I doing wrong here?
Click to expand...

Since [Amount] is a calculated field just sum the calculation:

=Sum(IIf([SC]="731" Or [Provider type]=1,[Payrate]* [Units],
[Payrate]*[Units]*1.1175))

- Jim

.
Click to expand...
 
I can't say for sure, but it sounds like there is a problem with the
[Provider type] side of the condition.

I would investigate that; spelling, typos, etc.

- Jim

Thanks, Jim. Although it's a little OT, I'd like to ask you about the
related problem I'm having with this calculation. I need to make the
same calculation [Amount] in a Query, so I used:

Amount: IIf([SC]="731" Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.1175)

It returns #Error in all records for the field [Amount] except those
where [SC]="731". I don't understand it at all. I posted this problem on
the Query group but no one seemed to know the solution. Maybe you
have some insight?

Thanks again,
spence

-----Original Message-----
I have a subform calculated field [Amount] that looks like
this:

=IIf([SC]="731" Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.1175)

It works fine, but I have a calculated form in the footer
of the subform that is supposed to sum the line items in
field [Amount]:

=Sum([Amount])

The sum it gives me ignores the multiplier 1.1175 in all
cases even though the [Amount] line items display the
correctly multiplied amount.

What am I doing wrong here?
Click to expand...

Since [Amount] is a calculated field just sum the calculation:

=Sum(IIf([SC]="731" Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.1175))

- Jim

.
Click to expand...
Click to expand...
 
Thanks, Jim. Although it's a little OT, I'd like to ask you about the
related problem I'm having with this calculation. I need to make the
same calculation [Amount] in a Query, so I used:

Amount: IIf([SC]="731" Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.1175)
Click to expand...
You have a space in provider type and not in payrate.
While allowed I hate spaces since pay rate and pay rate are very hard to
distinguish at 3:00 AM.
I think PayRate and ProviderType would be a better choice and you don't need
those stinkin' [ ]
 
Back
Top