Generate a table with a list of numbers between the given range

Hannah_0987

New member
Local time
Today, 14:57
Joined
Sep 19, 2024
Messages
2
How do I build program that can generate a table that lists out the numbers within the range I have entered in a form.

The user inserts two numbers in a form. For example, the field StartValue is 1 and EndValue is 20.
In another table, I would like numbers from StartValue to EndValue to be listed out. For example, 1,2,3,4...20. Each number should be listed out row by row, so in this case when I've entered 1 and 20, I'll get 20 rows of numbers.
For reference, the function is similar to the Series function in Excel as the picture below, but its implemented in Access.
Screenshot 2024-09-20 001804.png

Any help would be appreciated, thank you.
 
First--why? We are always leery of people with low post counts asking unique questions without context. What is the end goal, what will doing this task help with?

With that said, you could write VBA to run a bunch of INSERT statements. You could build a base table with a ton of numbers and then use it as the basis of a MAKE TABLE query and use the input values as criteria.

Again though, there might be a better way to achieve the ultimate aim than this intermediary step.
 
As @plog said this would be pretty simple to do with a loop calling an insert query for the rows, but may be problematic with columns.

You could do 255 Fields Maximum. So you could make a dummy table with 255 fields, but that is your limit. However the problem would be that you would need to likely make a numeric table and date table. You could build these tables dynamically but that would be more code intensive.
But again what is it you really want to do? There may be a better idea.
 
I agree with plog, requests like this are always suspect because generating "empty" records is not recommended. One case where I have done this was with a receiving system. The user wanted to enter a description and then a quantity and have x records generated. So "white sheets", "10 cases". The individual rows were necessary so that the pickers could be instructed to issue box 1 before box 2 to keep inventory from getting stale when they were shipping it out.

The simplest way to do something like this it to create what we call a tally table.

 
here is a demo, open demo_series form and click Generate Series button.
open the form in design view and see the VBA (Click event) behind the Generate series button.
 

Attachments

I answered a similar question a while ago.

I posted it on my website here:-


A sample database demonstrating the process is available from the Website for free just use the coupon code:- "BuyMeACoffee"
 
Last edited:
here is a demo, open demo_series form and click Generate Series button.
open the form in design view and see the VBA (Click event) behind the Generate series button.
Thank you, this is exactly what I needed. In response to the first few comments this is just intended for learning purposes that I might use for a future program. Once again I appreciate all your help :))
 
I keep table that has every integer from 1 to 100,000 and its text equivalent. It is useful for a variety of purposes.
 

Attachments

I use a table of 10 numbers (0-9) then a cartesian query using multiple aliases of the table to generate a range from 0 to 99, 999 ...999999. Primarily used to generate a calendar of dates and times. Typically 9999 is sufficient (around 27 years) and by adding to a specified date the range can be moved up and down the centuries.

The base cartesian query is

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

and for a calendar with 10 minute time slots (this example for today and hours between 8am and 6pm)

Code:
SELECT #1/1/2020#+[days].[counter] AS cDate, [hours].[Counter]*600/86400 AS cTime
FROM usysCount AS Days, usysCount AS Hours
WHERE (((#1/1/2020#+[days].[counter])=Date()) AND (([hours].[Counter]*600/86400) Between #08:00:00# And #17:59:00#))
ORDER BY #1/1/2020#+[days].[counter], [hours].[Counter]*600/86400

1727968416658.png

change the 600 for any time period you require in seconds (so hourly would be 3600 for example)

You would left join this to a table of appointments for example to produce a schedule
 
My surgery must use that method for our appointments. We only get 10 mins. :)
 
that example was for a doctors surgery :giggle:
 
As an alternative to having a table with a list of dates in it, you could employ a callback function of a combobox to fill the combo box with a list of dates ...

See a demonstration in this YouTube video showing my date picker working .

Nifty date picker with year selector


In the video I also mentioned Crystal Long as she has written some interesting code for selecting dates.... Now I may have the wrong end of the stick here but I seem to recall that Crystals code also delves into the computer setup and can automatically change the language the dates appear in - the Week names, from the PCs default language....


Chrystal"s YouTube
Row sourse type callback Function in Microsoft Access

 

Users who are viewing this thread

Back
Top Bottom