PASSING VALUES IN CALCULATED FIELDS ON FORMS.. PLEASE HELP

[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.

 

[KARL DEWEY]

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, ....

 

[clawdogs]

Thanks!!! That worked. It was the text issue. I really appreciate
the 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, ....
--
KARL DEWEY
Build a little - Test a little

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.

 

[clawdogs]

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.