updatequery problem

[fvd_1]

Hi,

I'm having difficulties finding the corerct SQL-code for an updatequery.
This query needs to define the record which has the highest numeral-value and update the new record with this number+1
For example; there are 3 records in a table, numbered 1,2 and 3
3 is the highest number, so the new record needs be updated from 0 to 4 (0 is the standardvalue for new records).
It's easy to define the highest value (with 'MAX') but not easy apparently to increase the new record with this value+1.
Here are some experiments of mine;

UPDATE facturen SET facturen.Factuurnr = "Max[factuurnr]"+1
WHERE (((facturen.Factuurnr)=0))
WITH OWNERACCESS OPTION;

But he does not accept the code: Max[factuurnr]

But if i try this;

UPDATE facturen SET facturen.Factuurnr = [factuurnr]+1
WHERE (((facturen.Factuurnr)=0))
WITH OWNERACCESS OPTION;

he accepts the code but it isn't any good offcourse because he has to increase 3 to 4 and not 0 (standard) to 1...
So if anyone knows a solution i would be very happy!
I prefere a solution in SQL to use in a query and not in VBA because i don't know any VBA unfortunately.

Thanks already!!!
Greetz

 

[ReAn]

UPDATE facturen SET facturen.Factuurnr = DMax("[factuurnr]","fracturen") + 1 WHERE (((facturen.Factuurnr)=0)) WITH OWNERACCESS OPTION;

 

[fvd_1]

eternal gratefull ;-)

Thank you very much ReAn!

Brilliant, it works! (offcourse it works you are thinking)
A huge relief, now i can continue making my database...

Many greetings!
fvd_1