Using the result of a calculated field in another calculated field's expression

[Paul]

I am stuck on something that seems fairly straightforward but haven't
found a solution anywhere! I understand that calculated fields are
temporary and the data is not stored as such. But I figure the
calculation is done when another command requests the information.
However this does not seem to be the case here:

Test Total before VAT NIS: IIf([Currency]="GBP",[Exchange
rate]*(([Hourly rate]*([Hours]))+[Fixed price]),([Hourly
rate]*([Hours]))+[Fixed price])

Test Invoice Total NIS: [Test Total before VAT NIS]*([VAT rate]/100)

The second field has a calculation based on the first field. However
when I try to run it, I get the error "Enter Parameter Value: Test
Total before VAT NIS" and a field to enter the value. It seems to me as
though the second field is not asking the first for a value or that
they are being run in the wrong order. I am a real newbie so if I'm
being an idiot, please tell me!

(By the way, I realise the field names are less than optimal but for
now I'm just messing around)

Thanks in advance!

Paul

 

[John Spencer]

In Access SQL, in most cases, you will have to repeat the original
calculation in the new calculation. That does make for a messy situation at
times.

Test Total before VAT NIS: IIf([Currency]="GBP"
,[Exchange rate]*(([Hourly rate]*([Hours]))+[Fixed price])
,([Hourly rate]*([Hours]))+[Fixed price])

Test Invoice Total NIS: IIf([Currency]="GBP"
,[Exchange rate]*(([Hourly rate]*([Hours]))+[Fixed price])
,([Hourly rate]*([Hours]))+[Fixed price]) * ([VAT rate]/100)



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

 

[Paul]

Thanks for that. That worked a treat. But don't you find it strange
that this is necessary? My original method of quoting a calculated
field in another calculation worked fine in a form, just not in a
query. Was I on the right track as to the reason for the 'problem'?

 

[John Spencer]

Do I find it strange? Yes and no.

I can see why the designers made the decision. In a query, you might be
applying criteria against the calculation. Unless you apply criteria
against a column the where clause knows nothing about the column or its
values. And the where clause is probably applied before the rows are
returned and the calculations are done. So if you use the results of one
calc in another calc and try to apply criteria against the second calc ---
ERROR. Mind you that is all a guess.

In a form, the values are already available for the record. So unless you
manage to do something that is circular, the code probably can resolve which
values it needs to calculate first so they can be used elsewhere in the
form.

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

Thanks for that. That worked a treat. But don't you find it strange
that this is necessary? My original method of quoting a calculated
field in another calculation worked fine in a form, just not in a
query. Was I on the right track as to the reason for the 'problem'?

In Access SQL, in most cases, you will have to repeat the original
calculation in the new calculation. That does make for a messy situation
at
times.

Test Total before VAT NIS: IIf([Currency]="GBP"
,[Exchange rate]*(([Hourly rate]*([Hours]))+[Fixed price])
,([Hourly rate]*([Hours]))+[Fixed price])

Test Invoice Total NIS: IIf([Currency]="GBP"
,[Exchange rate]*(([Hourly rate]*([Hours]))+[Fixed price])
,([Hourly rate]*([Hours]))+[Fixed price]) * ([VAT rate]/100)