Update Query: +1 to max value

[koumelas]

Hello,



i am trying to make an update query which every time it runs will update the table Invoice_Temp adding 1 to the max InvoiceNo value (this field has default value=0).
Here's what i have thought, but not working:

UPDATE Invoice_Temp SET InvoiceNo = (select max(InvoiceNo)+1 from Invoice_Temp)
WHERE InvoiceNo="0";

Can you help?

[Mahendra1000]

Could you kinldy post the error message here

[koumelas]

I translate, because it's not in English: "The function must use a query with update capabilities".

[Mahendra1000]

Hello,

i am trying to make an update query which every time it runs will update the table Invoice_Temp adding 1 to the max InvoiceNo value (this field has default value=0).
Here's what i have thought, but not working:

UPDATE Invoice_Temp SET InvoiceNo = (select max(InvoiceNo)+1 from Invoice_Temp)
WHERE InvoiceNo="0";

Can you help?

It means that the max InvoiceNo should be 1. Isn't it?

We can write it directly as

UPDATE Invoice_Temp SET InvoiceNo = 1 WHERE InvoiceNo="0";

As per your query :

UPDATE Invoice_Temp SET InvoiceNo = (select max(InvoiceNo)+1 from Invoice_Temp)
WHERE InvoiceNo="0";

As max(InvoiceNo) will be 0 as per your query

[koumelas]

I want everytime i run it to get value 1, 2, 3, 4, ... ,n. I don't want to put static numbers.

[koumelas]

Let me explain you. I have a query which imports rows from a table in Invoice_Temp table.
These rows that every time are added in Invoice_Temp table, i want them all to take an ascending number.
For example:
If i add 3 rows, i want them all to take the value "1" in InvoiceNo value,
then If i add another 2 rows, i want them all to take the value "2" in InvoiceNo value.

[JoeM]

I translate, because it's not in English: "The function must use a query with update capabilities".

It is because you are using an Aggregate Function/Query in your Update Query.
Access is very fickle and will not let you run an Update Query if any query involved in that query is not an updateable query (and Aggregate Queries are not updateable).

You may need to approach this from a different angle, like using VBA.
When do you want this to run?
How are new records added to your Invoice_Temp table?
How is the zero being populated for new records?

[Mahendra1000]

I think Query needs to be like this...

UPDATE Invoice_Temp SET InvoiceNo = max(InvoiceNo)+1 where -- add your condition here#

Kinldy excuise me if it still doesn't work at all.....

[koumelas]

JoeM
thank you. If there is nothing i can do to add 1 to the field's max value in sql,
can you tell me the way to write it in vba?

Mahendra1000
thank you, but it doesn't work.

[JoeM]

I think Query needs to be like this...

UPDATE Invoice_Temp SET InvoiceNo = max(InvoiceNo)+1 where -- add your condition here#

Kinldy excuise me if it still doesn't work at all.....

It won't work for the reasons I explained (you cannot use an Aggregate Query in an Update Query).
Try it you will see (it is always a good idea to try out the suggestions before posting them, whenever possible, to confirm that they will work).

thank you. If there is nothing i can do to add 1 to the field's max value in sql,
can you tell me the way to write it in vba?

That is why I asked all those questions in my previous post. In order to determine where to put the VBA and figure out how it needs to work, we need to get a complete understanding of your work-flow, of how and when these records are added.

[koumelas]

Problem solved. Here's my solution:

UPDATE Invoice_Temp SET InvoiceNo = DMax("InvoiceNo","Invoice_Temp")+1
WHERE InvoiceNo=0;

Thank you all for your help.

[JoeM]

Ah yes, forgot about the DMAX function. A clever way to get around the Aggregate Query issue.
Nicely done!