Number list

[keithhartley64]

How do I create a simple column of sequential numbers?

[orange]

Without more context, here is some code to build a 1 column table and populate it with sequential numbers.
You must supply a value for the nums parameter.

eg. BuildNumbersTable (500) will build and populate 1 thru 500

Function BuildNumbersTable(nums As Long) As Long

    'Create and fill a numbers table - 10000 is a good start
    Dim rs As DAO.Recordset
    Dim X As Long
    DoCmd.RunSQL "CREATE TABLE tblNumbers([Num] long PRIMARY KEY)"
    Set rs = CurrentDb.OpenRecordset("tblNumbers")
    For X = 1 To nums
        rs.AddNew
        rs(0) = X
        rs.Update
    Next
    rs.Close
    MsgBox "Table Built"
End Function

[mike60smart]

Hi Keith
Can we ask why you need a column of numbers?

[madpiet]

Hi Keith
Can we ask why you need a column of numbers?

So he can do cool things without stupid VBA loops?

[Bulzie]

Autonumber datatype?

[madpiet]

Works okay unless something odd happens and one of the inserts does something stupid. Then the number gets skipped.

[ArviLaanemets]

Or...
Create an single-column Excel table, enter a couple of starting numbers, and copy down;
Link the Excel table into Access DB
In case those numbers are used repeatedly in Acceess DB, use INSERT INTO query to fill your Access table with numbers from linked Excel table, and after that remove the linked table - otherwise just do what you wanted, and then remove the link.

Another way:
Create an Access table (e.g. tblNumberList) for numbers (e.g. with field Num), and enter manually numbers e.g. 0 - 9;
Create an INSERT QUERY like;
INSERT INTO tblNumberList SELECT (1*10)+Num from NumberList;
Run the query for multipliers from 1 to 9;
Repeat the previous cycle with 1*100 instead of 1*10;
Repeat the previous cycle with 1*1000 instead of 1*100;
etc.

I think it takes not much time until you reach the limit for number size.
And in case this is too much of manual work, create a multi-cycle procedure, which runs the same query instead.

[CJ_London]

I use a numbers list similar to avril’s but I just use the query rather than changing it into an append query.

edit: now back online so this is what I do

1. create a table with a single field and populate with the values 0 to 9 (my table is called usysCounter and the field, num)
2. use this query (I called it qryCounter) to generate numbers from 0 to 9999

SELECT CLng([singles].[num]+([tens].[num]*10)+([hundreds].[num]*100)+([thousands].[num]*1000)) AS [Counter]
FROM usysCounter AS singles, usysCounter AS tens, usysCounter AS hundreds, usysCounter AS thousands;

you can add more if you need over 9999
3. use another query to restrict the range or create dates e,g, this one creates a range of 100 days from today

SELECT date()+counter as dateRange
FROM qryCounter
WHERE counter <100