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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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 MVPDesigner and DeveloperCommented:
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 MVPDesigner and DeveloperCommented:
You're welcome. Glad I could assist.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.