not updateable query message

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hey all,

how do i make this work in Access:

UPDATE Transactions
SET InvNo = ( SELECT MAX( InvNo ) + 1
FROM Transactions t1 )
WHERE InvNo IS NULL ;

thanks,
rodchar
 
Hi,


The easiest way is to use DMAX.


UPDATE transactions
SET invNo = Nz( DMax("invNo", "transactions"), 0) + 1
WHERE invNo IS NULL;


but that will replace all occurrences of null with the SAME value
(1+the_max). If there are two records with a null and the max is 6, then
after update, both records initially with a null will both get 7.


Hoping it may help,
Vanderghast, Access MVP
 
thanks everyone, this helped.

Michel Walsh said:
Hi,


The easiest way is to use DMAX.


UPDATE transactions
SET invNo = Nz( DMax("invNo", "transactions"), 0) + 1
WHERE invNo IS NULL;


but that will replace all occurrences of null with the SAME value
(1+the_max). If there are two records with a null and the max is 6, then
after update, both records initially with a null will both get 7.


Hoping it may help,
Vanderghast, Access MVP
Click to expand...
 
Back
Top