awd2000

Probably being very dense but...

If I update a database table field in an "update" action
query, how do I get it to use the updated value of this field
in another "update to" expression in the same query?

At the moment I'm only getting the "before update" value
of the first field being used in the update expression of
the second one.

GaryK

Its not possible, each SQL command is a unit of work or a transaction, the new values will not get applied to the columns until the whole statement has executed this isnt an access 2002 issue its standard SQL. Can you post what it is you are trying acheive maybe another way.

cheers

Gary

awd2000

Thanks Gary

In a single-pass update query I am trying to..
1. Set 'Price' to a new value from a fairly complex calc
2. Set 'VAT' to 17.5% of this new value
3. Set 'Total' to the new value of 'Price' plus the calc value of 'VAT'

For a number of arcane reasons, the underlying table fields can't have expressions associated with them, hence using a query.

I've worked around the problem by...
1. Setting 'Price' =(complex calc expression)
2. Setting 'VAT' = (complex calc expression)*0.175
3. Setting 'Total' =((complex calc expression)+((complex calc expression)*0.175)

Not very elegant and a pain to maintain!

GaryK

No problem, one of the industry strength DBMS such as SQL server would let you get around this by using stored procedures which would let you store these calcuations in variables before running the update statement. Dont know if you know but you can get a cut-down version of SQL Server called MSDE which is a free download. It makes deployment a little trickier but is way more robust and scalable than an access solution.

cheers

Gary

awd2000

Thanks - I'll get this round of changes done and dusted and then I guess it's time to play with a real RDBMS!

Cheers