Calculated Field in Not Recognized in Function

[TR]

Hi Access MVPs,

I'm using Access 2003. I have a built a query that creates calculated
values. For example:

Price: [MatShip]+([MatShip]*[Markup])+[MatCredit]+
([LaborEngHrs]*[LaborEngPrice])+([LaborManuHrs]*[LaborManuPrice])+
[MngAdjust]

This part works fine. I'm able to use this calculated value in
another field in the same query to create another calculated value.
For example:

Difference: [Price]-[Cost]

What I'm having trouble with is that I'm trying to use that same
calculated value [Price] in the same query in a function. For
example:

IIf([Price]=0,1,2)

I'm getting prompted with "Enter Parameter Value" for "Price". Can
calculated values not be used in functions? Any thoughts?

Thanks in advance for your help.

- Tony

 

[Duane Hookom]

I don't use aliases in other calculations in a query. Consider replacing the
calculated field name with the full expression.

 

[KARL DEWEY]

One thing I have noticed (have not fully tested) is that if the alias is
located in the query design grid to the right of where it is to be use then
apparently it is processed first and then is available.
--
KARL DEWEY
Build a little - Test a little

I don't use aliases in other calculations in a query. Consider replacing the
calculated field name with the full expression.

--
Duane Hookom
Microsoft Access MVP

Hi Access MVPs,

I'm using Access 2003. I have a built a query that creates calculated
values. For example:

Price: [MatShip]+([MatShip]*[Markup])+[MatCredit]+
([LaborEngHrs]*[LaborEngPrice])+([LaborManuHrs]*[LaborManuPrice])+
[MngAdjust]

This part works fine. I'm able to use this calculated value in
another field in the same query to create another calculated value.
For example:

Difference: [Price]-[Cost]

What I'm having trouble with is that I'm trying to use that same
calculated value [Price] in the same query in a function. For
example:

IIf([Price]=0,1,2)

I'm getting prompted with "Enter Parameter Value" for "Price". Can
calculated values not be used in functions? Any thoughts?

Thanks in advance for your help.

- Tony

 

[Tony Rusin]

Hi Duane,

I tried that but it brings me to two more issues. One is that some of
those fields in the [Price] calculation are aliased subqueries. The
other is on the programmatical side where I'd have the same expression
written out multiple times. If at all possible, I'd prefer to have
the expression written once and reference it with an alias (like a VBA
function). Are you aware of another approach?

Thanks again,

- Tony

I don't use aliases in other calculations in a query. Consider replacing the
calculated field name with the full expression.

--
Duane Hookom
Microsoft Access MVP

Hi Access MVPs,

I'm using Access 2003. I have a built a query that creates calculated
values. For example:

Price: [MatShip]+([MatShip]*[Markup])+[MatCredit]+
([LaborEngHrs]*[LaborEngPrice])+([LaborManuHrs]*[LaborManuPrice])+
[MngAdjust]

This part works fine. I'm able to use this calculated value in
another field in the same query to create another calculated value.
For example:

Difference: [Price]-[Cost]

What I'm having trouble with is that I'm trying to use that same
calculated value [Price] in the same query in a function. For
example:

IIf([Price]=0,1,2)

I'm getting prompted with "Enter Parameter Value" for "Price". Can
calculated values not be used in functions? Any thoughts?

Thanks in advance for your help.

- Tony- Hide quoted text -

- Show quoted text -

 

[Tony Rusin]

Hi Karl,

I've encountered similar scenarios over the years and the function is
on the far right of the design grid but in this case it doesn't seem
to be so.

Thanks,

- Tony

One thing I have noticed (have not fully tested) is that if the alias is
located in the query design grid to the right of where it is to be use then
apparently it is processed first and then is available.
--
KARL DEWEY
Build a little - Test a little

I don't use aliases in other calculations in a query. Consider replacing the
calculated field name with the full expression.

Hi Access MVPs,
I'm using Access 2003. I have a built a query that creates calculated
values. For example:
Price: [MatShip]+([MatShip]*[Markup])+[MatCredit]+
([LaborEngHrs]*[LaborEngPrice])+([LaborManuHrs]*[LaborManuPrice])+
[MngAdjust]
This part works fine. I'm able to use this calculated value in
another field in the same query to create another calculated value.
For example:
Difference: [Price]-[Cost]
What I'm having trouble with is that I'm trying to use that same
calculated value [Price] in the same query in a function. For
example:
IIf([Price]=0,1,2)
I'm getting prompted with "Enter Parameter Value" for "Price". Can
calculated values not be used in functions? Any thoughts?
Thanks in advance for your help.
- Tony- Hide quoted text -

- Show quoted text -

 

[John Spencer]

Save the query with the calculation, but without the IIF calculation.

Use the query as the source for another query. The calculated field -
Price - will be available for use in the second query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi Duane,

I tried that but it brings me to two more issues. One is that some of
those fields in the [Price] calculation are aliased subqueries. The
other is on the programmatical side where I'd have the same expression
written out multiple times. If at all possible, I'd prefer to have
the expression written once and reference it with an alias (like a VBA
function). Are you aware of another approach?

Thanks again,

- Tony

I don't use aliases in other calculations in a query. Consider replacing the
calculated field name with the full expression.

--
Duane Hookom
Microsoft Access MVP

Hi Access MVPs,
I'm using Access 2003. I have a built a query that creates calculated
values. For example:
Price: [MatShip]+([MatShip]*[Markup])+[MatCredit]+
([LaborEngHrs]*[LaborEngPrice])+([LaborManuHrs]*[LaborManuPrice])+
[MngAdjust]
This part works fine. I'm able to use this calculated value in
another field in the same query to create another calculated value.
For example:
Difference: [Price]-[Cost]
What I'm having trouble with is that I'm trying to use that same
calculated value [Price] in the same query in a function. For
example:
IIf([Price]=0,1,2)
I'm getting prompted with "Enter Parameter Value" for "Price". Can
calculated values not be used in functions? Any thoughts?
Thanks in advance for your help.
- Tony- Hide quoted text -

- Show quoted text -

 

[Dale Fye]

I've also encountered instances where if I fully qualify the field names in
the computed column, that Jet is able to resolve the computed column name
elsewhere, although I cannot remember if I ever tried passing the computed
column to a function. Something like:

Price: tbl1.MatShip + (tbl1.MatShip * tbl1.MatShip.Markup) + tbl1.MatCredit
......

Dale