Referencing a Caculated Field in another Calculated Field

  • Thread starter Thread starter Carlos
  • Start date Start date
C

Carlos

When my query runs, Access keeps on asking (via a message box) to provide a
value for a calculated field that I'm referencing within another calculated
field.

See below and let me know if anyone knows how to prevent this BESIDES
writing the whole calculation again in the second calculated field. What I'm
showing below is a VERY simplified version for illustration purposes.


COLUMN1 FORMULA
ORDER DATE: ENTRY_DATE_FOR_THIS_FORM

COLUMN2 FORMULA
DELIVERY DATE: IIF(DATE()-[ORDER DATE]<1,DATE()+30,DATE()+20)

When query runs, it prompts for "ORDER DATE". Now ORDER DATE is NOT defined
as a Parameter, so that's not it.

ORDER DATE is to the left of the DELIVERY DATE, ie, calculated first.

If I click OK on the message box without entering anything, the query runs
fine, but I don't want the user to be prompted for no reason causing
confusion.

Also, I know that if instead of using [ORDER DATE] inside the DELIVERY DATE
formula, I just type ENTRY_DATE_FOR_THIS_FORM, the issue is avoided.
However, as I mentioned before, this is an oversimplified example. The
formula in column 1 is long and complex. I can't paste it into every
instance where ORDER DATE is called within the formula in column 2.

How do I solve this?
 
Most of the time it will not work as it calls for the alias before it is
created.
It appears (I would not bet much on it) that Access processes the SQL from
the bottom up. So if your first calculation is closer to the end than where
you use it, it might work.

Best to use the orignal calculation again instead of alias.
 
Another way to solve this problem is by nesting queries. You create a base
query that does the complex calculation and has additional fields needed for
linking. Then you add that base query to your original query.

Simplified example follows
query 1 named qCalc_1
SELECT TableA.PrimaryKeyField
, (Field1 * 22 /Field2) as SimpleExample
FROM TableA

Now you can use that in a second query
SELECT TableA.PrimaryKeyField
, Field3-SimpleExample as MySubtraction
, Field4*SimpleExample as MyMultiplication
, IIF(SimpleExample>25,Field8, Field12) as ChooseFieldData
FROM TableA INNER JOIN qCalc_1
ON TableA.PrimaryKeyField = qCalc_1.PrimaryKeyField

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

KARL said:
Most of the time it will not work as it calls for the alias before it is
created.
It appears (I would not bet much on it) that Access processes the SQL from
the bottom up. So if your first calculation is closer to the end than where
you use it, it might work.

Best to use the orignal calculation again instead of alias.

Carlos said:
When my query runs, Access keeps on asking (via a message box) to provide a
value for a calculated field that I'm referencing within another calculated
field.

See below and let me know if anyone knows how to prevent this BESIDES
writing the whole calculation again in the second calculated field. What I'm
showing below is a VERY simplified version for illustration purposes.


COLUMN1 FORMULA
ORDER DATE: ENTRY_DATE_FOR_THIS_FORM

COLUMN2 FORMULA
DELIVERY DATE: IIF(DATE()-[ORDER DATE]<1,DATE()+30,DATE()+20)

When query runs, it prompts for "ORDER DATE". Now ORDER DATE is NOT defined
as a Parameter, so that's not it.

ORDER DATE is to the left of the DELIVERY DATE, ie, calculated first.

If I click OK on the message box without entering anything, the query runs
fine, but I don't want the user to be prompted for no reason causing
confusion.

Also, I know that if instead of using [ORDER DATE] inside the DELIVERY DATE
formula, I just type ENTRY_DATE_FOR_THIS_FORM, the issue is avoided.
However, as I mentioned before, this is an oversimplified example. The
formula in column 1 is long and complex. I can't paste it into every
instance where ORDER DATE is called within the formula in column 2.

How do I solve this?
Click to expand...
Click to expand...
 
Karl, John, neither of these solutions are viable for me, but I very much
appreciate the input.

Thanks

Carlos

John Spencer MVP said:
Another way to solve this problem is by nesting queries. You create a base
query that does the complex calculation and has additional fields needed for
linking. Then you add that base query to your original query.

Simplified example follows
query 1 named qCalc_1
SELECT TableA.PrimaryKeyField
, (Field1 * 22 /Field2) as SimpleExample
FROM TableA

Now you can use that in a second query
SELECT TableA.PrimaryKeyField
, Field3-SimpleExample as MySubtraction
, Field4*SimpleExample as MyMultiplication
, IIF(SimpleExample>25,Field8, Field12) as ChooseFieldData
FROM TableA INNER JOIN qCalc_1
ON TableA.PrimaryKeyField = qCalc_1.PrimaryKeyField

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

KARL said:
Most of the time it will not work as it calls for the alias before it is
created.
It appears (I would not bet much on it) that Access processes the SQL from
the bottom up. So if your first calculation is closer to the end than where
you use it, it might work.

Best to use the orignal calculation again instead of alias.

Carlos said:
When my query runs, Access keeps on asking (via a message box) to provide a
value for a calculated field that I'm referencing within another calculated
field.

See below and let me know if anyone knows how to prevent this BESIDES
writing the whole calculation again in the second calculated field. What I'm
showing below is a VERY simplified version for illustration purposes.


COLUMN1 FORMULA
ORDER DATE: ENTRY_DATE_FOR_THIS_FORM

COLUMN2 FORMULA
DELIVERY DATE: IIF(DATE()-[ORDER DATE]<1,DATE()+30,DATE()+20)

When query runs, it prompts for "ORDER DATE". Now ORDER DATE is NOT defined
as a Parameter, so that's not it.

ORDER DATE is to the left of the DELIVERY DATE, ie, calculated first.

If I click OK on the message box without entering anything, the query runs
fine, but I don't want the user to be prompted for no reason causing
confusion.

Also, I know that if instead of using [ORDER DATE] inside the DELIVERY DATE
formula, I just type ENTRY_DATE_FOR_THIS_FORM, the issue is avoided.
However, as I mentioned before, this is an oversimplified example. The
formula in column 1 is long and complex. I can't paste it into every
instance where ORDER DATE is called within the formula in column 2.

How do I solve this?
Click to expand...
Click to expand...
Click to expand...
 
You should be able to do it the way I said. Perhaps if you post the SQL of
your current query (that is not working) we can suggest a specific solution.

You might be able to use a sub-query in the FROM clause to get the calculated
value. The ability to do so is dependent on many factors but the most
important factor is that you have followed the guidelines for naming your
tables and fields (only letters, numbers, and underscore characters used and
start the name with a letter).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top