How do I create a simple column of sequential numbers?
How do I create a simple column of sequential numbers?
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
Code: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
Hi Keith
Can we ask why you need a column of numbers?
You can PM me if you need further help.
Good Reading https://docs.microsoft.com/en-gb/off...on-description
Autonumber datatype?
Works okay unless something odd happens and one of the inserts does something stupid. Then the number gets skipped.
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.
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
you can add more if you need over 9999Code: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;
3. use another query to restrict the range or create dates e,g, this one creates a range of 100 days from today
Code:SELECT date()+counter as dateRange FROM qryCounter WHERE counter <100
Last edited by CJ_London; 01-06-2024 at 04:25 AM.