[AccessD] A2k: Creating an Array from Start and End Numbers

[Stuart McLachlan]

Darren,

You generaly use a number table for dynamic data, you just use offsets.

Create a number table containing the numbers 0 to 999 as an example.

If you need the next 10 numbers starting from 34567  you would just 

"Select (num + 34567)  from tblNumbers where num < 10" 

The beauty of using such as table is that you can use it to generate any sequence you like for 
numbers or dates:

"Select (num * 2) from tblNumbers where num between 1 and 20"  will give you the first 20 
even numbers (2,4,6....40)

If today is Tuesday then , "Select (Date() + (num * 7) from tblNumbers where num < 10" will 
give you an array of the next 10 Tuesdays including today.
  
etc, etc

One good way to use it is  as the left side of a join in order show every number/date in a 
range whether or not there are matching records in the table on the right side of the join.


Once you have a number table in your system, you will find more and more uses for it. :)


-- 
Stuart


On 24 Mar 2015 at 14:18, Darren wrote:

> Stuart - This is for storing 'voucher numbers'. 
> Users will add the first and last numbers in a sequence and now using
> Gustav's clever code I can present a list of available voucher numbers
> using these first and last numbers.
> 
> These will never be static - always on the move so a fixed table won't
> suffice but it's what I had in mind first, until the client explained
> how their 'voucher' system works.
>