Update table with a numerical sequence or cycle

[skeletor]

I want to update a field with a sequence of numbers. For example, there are two fields in a table with 100 records. The first field is an autonumber with #'s 1 to 100. The second field is a number field named Shift. I want to start numbering the Shift field so that it starts with 1 and fills down until it gets to 5, then starts over again 1 thru 5 until it fills all 100 records. So as you go down the column for the Shift field, the numbers would go 1,2,3,4,5,1,2,3,4,5,1,2,3,4,5,1,2,3,4,5,1,2,3,4,5 etc. Is there any way to do this with a query or vb code? I pulling my hair out trying different ways. Cant figure it out!

 

[Darrylles]

Hi,

You need the 'mod' function which takes your autonumber (1 to 100), divides it by 5 and returns the remainder only.

So 6 Mod 5 returns 1, 7 Mod 5 returns 2, etc.
1 mod 5 returns 1, 4 mod 5 returns 4.

However, when the remainder is zero, you obviously get 0.
So 5 mod 5 is 0, 10 mod 5 is zero, 100 mod 5 is zero.

To get around this, when the return IS zero, you just add 5 to it (to give you 5 in all such instances - as you want).
----------------------------------------------------------

So, first create a simple form with the autonumber field on it and the Shift field.
Copy this sub code to the form:

Private sub MyModulo

dim MyDiv, MyMod

MyDiv = 5
MyMod = MyAutoField mod MyDiv
if(MyMod = 0) then MyMod = 5
Shift = MyMod

end sub

Then, in the <On Current> event of your form use this:

call MyModulo

If you have a lot of records, then you may want to add a button to the form that uses <movenext> to step through the records.

Re-post if you need help on that.

Kind regards,

Darrylle


&quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk