Drop down list of years

[Dave_cha]

Hi folks,

I want to populate a drop down combo box with a list of 10 years (current year +/- five years). I can enter them as a Value list but I don't want to have to update the list every year. Neither do I want to allocate a table to maintain a list.

Any ideas as to how I can auto populate the combo with a 10 yr span (5 previous and 5 next) and default to current year?

Thanks,

Dave

 

[seth_belgium]

You could do it like this:

Your listbox' name is lstYears.

Private Sub Form_Load()
Me.lstYears.AddItem Year(Date) - 5
Me.lstYears.AddItem Year(Date) - 4
Me.lstYears.AddItem Year(Date) - 3
Me.lstYears.AddItem Year(Date) - 2
Me.lstYears.AddItem Year(Date) - 1
Me.lstYears.AddItem Year(Date)
Me.lstYears.AddItem Year(Date) + 1
Me.lstYears.AddItem Year(Date) + 2
Me.lstYears.AddItem Year(Date) + 3
Me.lstYears.AddItem Year(Date) + 4
Me.lstYears.AddItem Year(Date) + 5
End Sub

Seth

 

[Dave_cha]

Thanks Seth......I had just worked out a similar solution.

Private Sub Form_Load()

Dim MinYr, MaxYr As Integer

MinYr = DatePart("yyyy", Date) - 5
MaxYr = DatePart("yyyy", Date) + 5

Do While MinYr <= MaxYr
Me.Year.AddItem MinYr
MinYr = MinYr + 1
Loop

Me.Year = DatePart("yyyy", Date)

End Sub

 

[john471]

How about 11 years ? 5 previous + current + 5 next ?

Here is ONE way to do it.

Create a table (I know you said you didn't want to maintain a table... just trust me...)

tblYearProjection
-----------------
YearOffset (PK) (number/integer)

Populate it with values -5,-4,-3,-2,-1,0,1,2,3,4,5 (11 records, 1 value per record)

Write a query

qryDisplayYear
--------------
SELECT Format(DateAdd("yyyy",[YearOffset],Date()),"yyyy") AS DisplayYear
FROM tblYearProjection;

assign the query as the combo's recordsource.

And all with NO CODE !

When you decide you want to change the span to +/-3 years, or +/-10years, then you need to "maintain" the table, otherwise it should just keep on trucking...

If you really didn't want current year in your list, delete the zero record from the table

HTH

Regards,

John

 

[john471]

Dim MinYr, MaxYr As Integer

Dave - FYI, the result of this DIM statement is the same as
Dim MinYr as Variant
Dim MaxYr as Integer

 

[Dave_cha]

Thanks John.

I'll probably stick with the code but it's useful to have the option.

Rgd's,

Dave

 

[boblarson]

I would also go the code route over using a table in this case too, since with the code there would never be the need for maintaining a table. This would make it more transparent to the user and they would not need to remember to do anything.

I find that it's a good idea that simplicity for the user is better than simplicity for the creator of the database/program.

 

[seth_belgium]

Long live coding I say! A database application needs to be able to run on its own and, when in final production stage, with as little as possible help from the administrator.

 

[john471]

I would also go the code route over using a table in this case too, since with the code there would never be the need for maintaining a table. This would make it more transparent to the user and they would not need to remember to do anything.

I find that it's a good idea that simplicity for the user is better than simplicity for the creator of the database/program.

Boblarson, it seems you didn't take the time to understand my suggestion.

a) My solution requires NO MORE maintenance than the coded solution(s) offered.
b) IF desired, the number of years in the span could be adjusted merely by modifying table records.
c) It offers the end user NO LESS simplicity - The end user would not need to "remember to do anything" in order for the years in the drop-down to update as time progresses.
d) It offers the administrator MORE simplicity - you could apply the concept in many forms, and IF they needed to be adjusted, adjust them all from the one place (table).
e) Because the concept uses a query, and not code, it could be extended to use in other queries, e.g. if you wanted all years (or months, or weeks, or days, or hours) based on a span around today's date/time, to show in a report, regardless of there being any data associated (such that gaps are identified) you could use such a setup as the starting point, with an outer join to the data.

That said, there's almost always more than one way to skin a cat, as they say, and you can skin yours however you want, but please don't reflect shortcomings onto my method that are not there.

Regards

John.

 

[raskew]

Hi-

Modify as necessary.

Function YearLoop() As String
'*******************************************
'Purpose: Create a dynamic string (based on
' the current year) of years to be
' used as a combo-box's value list,
' starting with current year -5
' through current year + 5
'*******************************************

Dim yearhold As Date
Dim strSQL As String
Dim i As Integer
Dim n As Integer

n = 5
strSQL = ""

For i = -5 To n
yearhold = DateSerial(Year(Date) + i, 1, 1)
strSQL = strSQL & Format(yearhold, "yyyy") & "; "
Next i

YearLoop = strSQL

End Function


HTH - Bob