How can I increment a new field

G

Garry

Hi

Say I have 500 records that has been sorted in ascending order (post code).
How can I increment a new field with 1,3,5 etc for the first 250 records
then the second 250 with 2,4,6 etc

regards, Garry
 
G

Garry

Thanks for your time Mike,

New to Access, where do I place this text, cheers Garry
 
K

kingston via AccessMonster.com

I don't think that this can be done efficiently with queries (at least not
with the given parameters). One way to do this is to make a procedure that
cycles through the recordset and assigns the new field value:

Public Sub AssignNew()

Dim rst1 As New ADODB.Recordset
Dim i As Integer

rst1.Open "SELECT TOP 250 [NewField] FROM [Existing RecordSet];",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rst1.MoveFirst
i = 1
While rst1.EOF = False
rst1!NewField = i
rst1.MoveNext
i = i + 2
Wend

rst1.Close

rst1.Open "SELECT [NewField] FROM [Existing RecordSet] WHERE ([NewField]
Is Null);", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rst1.MoveFirst
i = 2
While rst1.EOF = False
rst1!NewField = i
rst1.MoveNext
i = i + 2
Wend

End Sub

[Existing RecordSet] is your already sorted list.
 
Top