I need several thousand consecutive numbers.

  • Thread starter Thread starter MC
  • Start date Start date
M

MC

I need to create a table with just one field. This field is to contain
consecutive numbers 1700-30000.

Preferabley in steps of 5 i.e. 1700, 1705, 1710 etc.. However single steps
would be fine.

How can I do this without the obvious entering data by hand? Is there a way
I can do this in excel and import the data?

Regards

MC
 
I need to create a table with just one field. This field is to contain
consecutive numbers 1700-30000.

Preferabley in steps of 5 i.e. 1700, 1705, 1710 etc.. However single steps
would be fine.

How can I do this without the obvious entering data by hand? Is there a way
I can do this in excel and import the data?

Regards

MC
Click to expand...
Yes you can, and it's trivial. Send me your email address
([email protected]) and I'll mail you the file.


Charlie Hoffpauir
http://freepages.genealogy.rootsweb.com/~charlieh/
 
I need to create a table with just one field. This field is to contain
consecutive numbers 1700-30000.

Preferabley in steps of 5 i.e. 1700, 1705, 1710 etc.. However single steps
would be fine.

How can I do this without the obvious entering data by hand? Is there a way
I can do this in excel and import the data?

Regards

MC
Click to expand...

Add a new table to the database.
Table Name: tboOfNumbers
Field Name: Number Datatype Number
Field Size: Integer
====
Paste the following into a Module:

Public Sub FillATable()
' Will fill a table field with incremented numbers
Dim db As Database
Dim rs As Recordset
Dim lgCounter As Long
Dim intStart As Integer
Dim intEnd As Integer
Set db = CurrentDb

Set rs = db.OpenRecordset("tblOfNumbers", dbOpenDynaset)
intStart = InputBox("From?", "Start with #", 1700)
intEnd = InputBox("To?", "End with #", 30000)
For lgCounter = intStart To intEnd Step 5
With rs
.AddNew
![Number] = lgCounter ' lgNumber
.Update
End With
Next lgCounter
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub
======

I've given you an input box to enter a different set of numbers if you
wish. They default to 1700 and 30000.

Run the code.
 
Go into excel and type the first 4 or 5 numbers down one
coloumn
Hightlight the cells
Drap the bottom right hand corner (should look like a
little + symbol down until you get to the desired final
number,

Save it

Go to access and click File >> Import external data and
follow the wizard.

Done
 
I need to create a table with just one field. This field is to contain
consecutive numbers 1700-30000.

Preferabley in steps of 5 i.e. 1700, 1705, 1710 etc.. However single steps
would be fine.

How can I do this without the obvious entering data by hand? Is there a way
I can do this in excel and import the data?

Regards

MC
Click to expand...

An easier solution yet, though it takes some setting up:

Create a little table Num with one Long Integer field N. Manually fill
it with ten rows, 0 through 9.

Create a Query

SELECT Num.N * 5 + Num_1.N*10 + Num_2.N*100 + Num_3.N * 1000 + Num_4.N
* 10000 + 1700 AS SeqN
FROM Num, Num AS Num_1, Num AS Num_2, Num AS Num_3, Num AS Num_4
WHERE Num_4.N <= 2 AND Num.N <= 1;
 
John's solution is quite correct and the most righteous in database terms
but you could also get there using VBA (in this case, just plain old BASIC
from eons past):

Open a recordset, rst, based on your table for Edit
For i = 1700 to 30000 step 5
rst.Addnew
rst.num = i
rst.Update
next i
rst.close

HTH
 
Back
Top