Create list in access or excel

I need to create a list of numbers from 100-000 to 999-999 is this possible to do. I have only been able to find third party software that looks like it would work.

example
100-000 to 100-999 then
101-000 to 101-999 then
102-000 to 102-999 and so on until 999-999
Thanks
fasseAsked:
Who is Participating?
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
That can easily be done with an append query or by opening a recordset. Need to know the fielfd in the table to help with any additional code.

Why would you want to store all the valid part numbers?

I would not store all the valid part numbers unless there is a really good reason. It would be very easy to verify if the part number is valid.


Example using a recordset:

Dim I As Long

Dim J As Long

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblvalidparts")


For I = 100 To 999

    For J = 0 To 999
    
        rs.AddNew
        
        rs!PartNumber = Format(I, "000") & "-" & Format(J, "000")
        Debug.Print Format(I, "000") & "-" & Format(J, "000")
        
        rs.Update
        
    Next J


Next I

rs.Close
Set rs = Nothing

Open in new window

0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Sure it is possible with VBA. Where do you want the list to go?

Dim I As Long

Dim J As Long


For I = 100 To 999

    For J = 0 To 999
    
        Debug.Print Format(I, "000") & "-" & Format(J, "000")
    
    Next J


Next I

Open in new window


This example sends the list to the immediate windows
0
 
fasseAuthor Commented:
A table in access called tblvalidparts
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
fasseAuthor Commented:
I have a form set where someone looking for a new partnumber can start to type in the part and only the ones that have not been used show up.  
Example
you type 203-2
It will show you all the 203-2XX numbers that are available.
I am currently using this and everyone seems to like it. But we have used most of the numbers that I have done by had and was just looking for an easy way to create the list for every possible part number.

I will give this a try.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
I see.

Let me know if you can't get the code to run after changing to use your field name.
0
 
fasseAuthor Commented:
Worked perfect.  Thanks so much
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
You're welcome. Glad I could assist.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.