increment a value for every x records

P

pixie

i have some existing data and have to add a new field which increments from 1
for every 10 records in the table, is there any way to do this using an
expression in an update query or otherwise ?
 
O

Ofer

You can use a function

Function aaa()
Dim MyDB as database,MyRec as recordset, MyNum as Long,MyCount as integer
MyNum =1
MyCount =1
set MyDB=codedb
set myrec=mydb.openrecordset("Select * From MyTable")
While not myrec.eof
If MyCount=11 then
MyCount=1
MyNum = MyNum +1
End if
MyRec.edit
MyRec!FieldName = MyNum
MyRec.update
MyCount =MyCount +1
myrec.movenext
Wend

End Function

I didnt try it, but I hopr it works
 
P

pixie

thank u so much
i am trying it but i am getting a compile error user define type not defined
 
O

Ofer

Check your references, see if you missing anything. when you in code, go to
tools refernce, check if it says that anything is missing.
also check if you have refernce to Access 11, and DAO 3.6
 
Top