Array Question: Dynamic Monthly Column Headings

[nico3334]

I'm not that familiar with arrays so I'll try to explain my problem as clear as possible. I have 2 text boxes in VB where users are basically allowed to choose a date range up to 12 months. The format of the text boxes are "mm/dd/yyyy". So a user could choose to display data from 01/01/2007 to 12/01/2007. Or 07/01/2007 to 10/01/2007. Then I want the data to display under monthly column headings. My problem is due to the fact that the number of columns could change each time, so it will never be fixed . Thus, I'm having a problem creating an array with a loop to determine the first and last month choice, and fill in the months between.

The VB form is linked to a SQL database, so it is pulling the monthly data from this table. Just to give you an example, here is what column headings I would like to display if a user selects a date range of 01/01/2007 to 04/01/2007:

200701 —- 200702 —- 200703 —- 200704

I really don't need any SQL coding to accomplish this, I'm just not sure how to use the array and loop to populate these column headings. Thanks!

[kadghar]

check out the month and year functions, to create an array with the titles you want, if the initial date is in Date1 and the ending date is in Date2 just do something like this

dim namArray() as string
n = 12 * (year(date2) - year(date1)) + month(date2) - month(date1) +1
redim namarray(1 to n)
n=1
dateTmp = date1
do
    namarray(n) = month(datetmp) & "-" & year(datetmp)
    datetmp = dateadd("m",1,datetmp)
    n=n+1
    if datetmp > date2 then exit do
loop

hope that helps

[nico3334]

Thanks for your reply. I'm sorry, I've been trying to understand each line of your code but I seem to having some problems. Could you possibly add some comments so I can decode what some of the lines mean? Thank you so much.

[kadghar]

Thanks for your reply. I'm sorry, I've been trying to understand each line of your code but I seem to having some problems. Could you possibly add some comments so I can decode what some of the lines mean? Thank you so much.

sure

dim namArray() as string 'Define an array of strings
n = 12 * (year(date2) - year(date1)) + month(date2) - month(date1) +1
redim namarray(1 to n) 'here I put the dimension of the array
'Year(date) gives you back an integer with the year of the date
'month(date) gives you back an integer betwen 1 and 12 
n=1 'start a counter called n
dim dateTmp as date
dateTmp = date1  'create a dummy, i should have defined it first (done!)
'and now that dummy is the starting date
do  'start a loop
    namarray(n) = month(datetmp) & "-" & year(datetmp)  'add the n'th element to the array with the month and year of the dummy date
    datetmp = dateadd("m",1,datetmp) 'add one month to the dummy date
    n=n+1'increase the counter
    if datetmp > date2 then exit do 'if the dummy date is greater than the finishing date then i exit the loop
loop 'close the loop

[QVeen72]

Hi,

Check This :

Dim TDate As Date
TDate =CDate(txtFromDate.Text)
List1.Clear
Do
 List1.AddItem Format(TDate ,"YYYYMM"
 TDate = DateAdd("m", 1 , TDate)
Loop Until TDate>=CDate(txtToDate.Text)
 

Start with the First Date(txtFromDate), Add One Month and keep on Repeating till u reach the Last Date. Change the "List1.AddItem", to populate ur Grid Column/ Array..

REgards
Veena