PASSING VALUES IN CALCULATED FIELDS ON FORMS.. PLEASE HELP

  • Thread starter Thread starter clawdogs
  • Start date Start date
C

clawdogs

Hi. I have two fields on query. One field is calculated, the other
just pulls numeric data from a table:

CALCULATED FIELD:

TOV COMPONENT: IIf([Profile]="Hunter","70%",IIf([Profile]
="Grower","50%",IIf([Profile]="Farmer","20%",IIf([Profile]="Sales
Manager","50%","UNDECLARED"))))

REGULAR FIELD (as currency):

OTB:OTB

I'm trying to get the the following multtiplication equation to work
on a text field but it seem the calculated field is causing erors on a
form:

=[TOV Component] * [OTB]

basically I'm trying to multiply a percent field with a currency field
but the string value of the calculated field doesn't seem to
register. I used the same calculation on a non-calculated percent
field with a non-calculated currency field and it works fine. Does
anyone know how to capture the calculated field string so that I can
perform the equation without getting an error on a ffom? Please
help.
 
Two things wrong --

First MOST of the time you can not use the alias for calculation within the
same query that created it. You have to reuse the same math in the second
calculation.

Second - you can not multiply '70%' by a number as it is text. You IIF
statement needs to be like this --
IIf([Profile]="Hunter", .7,IIf([Profile] ="Grower", .5,
IIf([Profile]="Farmer", .2, IIf([Profile]="Sales Manager", .5, ....
 
Thanks!!! That worked. It was the text issue. I really appreciate
the help.

KARL said:
Two things wrong --

First MOST of the time you can not use the alias for calculation within the
same query that created it. You have to reuse the same math in the second
calculation.

Second - you can not multiply '70%' by a number as it is text. You IIF
statement needs to be like this --
IIf([Profile]="Hunter", .7,IIf([Profile] ="Grower", .5,
IIf([Profile]="Farmer", .2, IIf([Profile]="Sales Manager", .5, ....
--
KARL DEWEY
Build a little - Test a little


[email protected] said:
Hi. I have two fields on query. One field is calculated, the other
just pulls numeric data from a table:

CALCULATED FIELD:

TOV COMPONENT: IIf([Profile]="Hunter","70%",IIf([Profile]
="Grower","50%",IIf([Profile]="Farmer","20%",IIf([Profile]="Sales
Manager","50%","UNDECLARED"))))

REGULAR FIELD (as currency):

OTB:OTB

I'm trying to get the the following multtiplication equation to work
on a text field but it seem the calculated field is causing erors on a
form:

=[TOV Component] * [OTB]

basically I'm trying to multiply a percent field with a currency field
but the string value of the calculated field doesn't seem to
register. I used the same calculation on a non-calculated percent
field with a non-calculated currency field and it works fine. Does
anyone know how to capture the calculated field string so that I can
perform the equation without getting an error on a ffom? Please
help.
Click to expand...
Click to expand...
 
I also used your suggestion but it didn't work. It was the text thing
above. Thanks anyway. You helped me understand what Curr and CDbl
does... Thanks.
Try this first:

=CDbl([TOV Component]) * CCurr([OTB])

then let us know. If that doesn't work, please post the error message.

Hi. I have two fields on query. One field is calculated, the other
just pulls numeric data from a table:

CALCULATED FIELD:

TOV COMPONENT: IIf([Profile]="Hunter","70%",IIf([Profile]
="Grower","50%",IIf([Profile]="Farmer","20%",IIf([Profile]="Sales
Manager","50%","UNDECLARED"))))

REGULAR FIELD (as currency):

OTB:OTB

I'm trying to get the the following multtiplication equation to work
on a text field but it seem the calculated field is causing erors on a
form:

=[TOV Component] * [OTB]

basically I'm trying to multiply a percent field with a currency field
but the string value of the calculated field doesn't seem to
register. I used the same calculation on a non-calculated percent
field with a non-calculated currency field and it works fine. Does
anyone know how to capture the calculated field string so that I can
perform the equation without getting an error on a ffom? Please
help.
Click to expand...
Click to expand...
 
Back
Top