Generate list of consecutive numbers /years

S

S N

I want to generate a list of years starting from 1901 (say) to the current
year using SQL/Queries in access. I would like to show this list of years in
listbox in an asp page.
Kindly advise on the way to achieve the same.

S N
 
B

Brendan Reynolds

I can not see any way that this can be done using SQL alone. It can, of
course, be easily done in VBA ...

Public Sub AddYears()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngTheYear As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("tblYears")
For lngTheYear = 1901 To Year(Date)
rst.AddNew
rst.Fields("TheYear") = lngTheYear
rst.Update
Next lngTheYear
rst.Close
MsgBox "Finished"

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
S

S N

If I remember correctly someone had posted a solution to this problem about
an year back.
I still feel this is possible. I cant use VBA as I am using dynamic
generation of pages, which would not work without SQL/Queries in Access.

Please advise
 
G

Gary Walter

S N said:
I want to generate a list of years starting from 1901 (say) to the current
year using SQL/Queries in access. I would like to show this list of years in
listbox in an asp page.
Kindly advise on the way to achieve the same.
Click to expand...
Hi S N,

One method is to have a number table
that goes from 0 to largest-imaginable
year span

tblNum
Num
0
1
2
3
....

SELECT
(1901 + Num) AS EnumYear
FROM tblNum
WHERE
(1901 +Num) <= Year(Date());

or, using a parameter for starting year:

PARAMETERS [StartYear] Long;
SELECT
([StartYear] + Num) AS EnumYear
FROM tblNum
WHERE
([StartYear] +Num) <= Year(Date());


Creating a *large* tblNum table is easy
and Michel has detailed the method many
times on this newsgroup (this link may wrap
inappropriately).

http://groups.google.com/groups?&selm=#k8KyJYfBHA.1872@tkmsftngp02

Or the "Excel method" works just as well
to create tblNum.

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Top