Looping

[irade92]

For example
I have 20 records in a table with two fields...Code and Invoice
5 rec with code '037'
6 rec with code '223'
9 rec with code '280'
I want invoice to be updated with '0201' for all code '037', '0202' for all code '223' and '0203' for all code '280' with a looping
I made a looping but results at the end are..all invoices updated with '0203'
This is only example but tables can have a lot of records..
Please help

 

[jdraw]

You need 3 queries.

Update tablename
set invoice = '201'
where code ='037';

Update tablename
set invoice = '202'
where code ='223';

Update tablename
set invoice = '203'
where code ='280';

I just used your single quotes as example.
If these fields are numeric no quotes are needed.
If the fields are alpha then I would use double quotes ".

But if you already updated the data incorrectly, that is a different issue at this time.

 

[irade92]

As I said..this is just an example...the problem is I don't know what the Code should be..but I know what the starting number of Invoice should be...I need something like this ..
do Until rs.EOF()
rcode = rs("code")
do while rcode = rs("code")
do updatiing
loop
'change invoice number
loop
rs.close
and so on
..

After implemeting this I get the result ALL CODE GET THE LAST INVOICE NUMBER
I am wondering where is my mistake ..I used Local Window in VBA editor and everything seems fine..but in the table NOT

 

[Brianwarnock]

As the invoice values are hard coded then write a simple function of Select Case and use it in an Update query

Brian

Function updateinvoice(code As Long) As String

Select Case code
Case 201
updateinvoice = "037"
Case 202
updateinvoice = "223"
etc

End Select

End Function

UPDATE Tablename SET Tablename.invoice = updateinvoice(

);

 

[Brianwarnock]

Ok you posted while I was typing, but now I don't understand where you get your invoice number from, ie what is the relationship between the code and the invoice.

Brian

 

[irade92]

Ok you posted while I was typing, but now I don't understand where you get your invoice number from, ie what is the relationship between the code and the invoice.

Brian

Hi Brien
The invoice number came from a string made of month of date and starting number 01..i.e month(date) & IIF(len(format(number))=1,"0"&format(number),format(number))
The value in CODE field can vary each month..