Incremental 1, 4, 7, 10,

[Garry]

Hi

I would like the following ID field to be incremented to another field for
sorting on that field

ID from 1 - 1000, increment by three 1, 4, 7, 10, etc

ID from 1001 - 2000 increment by three 2, 5, 8, 11 etc

ID from 2001 - 3000 increment by three 3, 6, 9, 12 etc

can this be done please

 

[Access Developer]

I'm not quite sure what you are asking. (Just by the way, "increment by 3"
doesn't mean much unless you are clear as to what number is to be
incremented from.

Do you mean convert, by the formula 1 + (n-1)* 3 = m, convert from n to m,
as follows, where n is in the range 1 - 1,000:

1 -> 1 : 1 + (0*3) = 1 + 0 = 1
2 -> 4 : 1 + (1*3) = 1 + 3 = 4
3 -> 7 : 1 + (2*3) = 1 + 6 = 7
4 -> 10 : 1 + (3*3) = 1 + 9 = 10
.. . .
1000 -> 2998 : 1 + (999*3) = 1 + (2997) = 2998

then convert, by the formula 2 + (n-1001)*3 = m, where n is in the range
1,001 - 2,000:

1001 -> 2 + (0*3) = 2 + 0 = 2
1002 -> 2 + (1*3) = 2 + 3 = 5
1003 -> 2 + (2*3) = 2 + 6 = 8
.. . .
2000 -> 2 + (999*3) = 2 + 2997 = 2999

and convert, by the formula 3 + (n - 2001)*3 = m, where n is in the range
2,001 - 3,000:

2001 -> 3 + (0*3) = 3 + 0 = 3
2002 -> 3 + (1*3) = 3 + 3 = 6
2003 -> 3 + (2*3) = 3 + 6 = 9
.. . .
3000 -> 3 + (999*3) = 3 + 2997 = 3000

But, I'm wondering, if you couldn't figure out formulae that simple, are you
really accomplishing what you want? Maybe a description in words of what
you are trying to accomplish would be helpful.

Here's some aircode (that is, it's untested and has no error handling) that
should accomplish the above:

Function TrnsForm(intInput As Integer) As Integer
Dim intSub As Integer, intBase As Integer
Select Case intInput
Case 1 To 1000
intSub = 1
intBase = 1
Case 1001 To 2000
intSub = 1001
intBase = 2
Case 2001 To 3000
intSub = 2001
intBase = 3
Case Else
intSub = 0
intBase = 0
End Select
TrnsForm = intBase + (intInput - intSub) * 3
End Function

 

[Garry]

Perhaps a description is more appropriate

I have to laser 3,000 DL tickets (99mm x 210mm) that are sequentially
numbered 1 - 3000

I will print on an A4 card via my laser printer

If I print three to view as it stands and guillotine into three piles
in order to maintain order I have to keep taking tickets from the top of
every pile

I want to be able to guillotine and place pile1 on top pile2 underneath and
pile3 at the bottom
to make a single stack

I can then just keep taking the top ticket from the single stack

Garry

ps I could have 25,000 tickets in future