Split dates into tables

[JohannIcon]

Hi All,

I have a table with three fields, ie Id, Date and Link. Now the table is populated with a lot of records, all having their different ID number, date and link. Now I wish to create a table in HTML, which can be presented to the user. I wish to display these items first by Year and then by Month.

This is how I wish to have my HTML table composed for example:-

YEAR 2002
MONTH
Nov
11/28/2002

http://www.link1.com

11/27/2002

http://www.link2.com

11/20/2002

http://www.link3.com

Oct
10/25/2002

http://www.link4.com

10/23/2002

http://www.link5.com

10/05/2002

http://www.link6.com

Sep
09/22/2002

http://www.link7.com

etc.

The problem is that I need to create all this from a simple date field. I ran out of ideas, can you help me?

Thanks for your help

 

[neilharris]

If you select the data in reverse date order in the sql then all you need to do is create a variable to keep track of the year and a variable to keep track of the month.

If the year part of the current record is less than the variable year, output a new year heading and set the variable year equal to the records year.

Then do the same check for the month.

Then output the record.

Neil

 

[JohannIcon]

Yeah I know I can do that, infact that is what I have done on paper as a pseudo-code, however when I came to code it, i found it very hard to achieve, that is why I tried to call for help on this forum

 

[Tarwn]

Ok, there is a year function and a month function to get the year and month of a date.
This is only pseudo code, but should be logically sound:

dim curYear, curMonth
dim workingDate
Do Until recordset.EOF
workingDate = recordset("yourdatefield")
If year(workingDate) <> curYear Then
   Response.Write &quot;<tr><td>&quot;&year(workingDate)&&quot;</td>&quot;
   curYear = year(workingDate)
Else
   Response.Write &quot;<tr><td></td>&quot;
End If

If month(workingDate) <> curMonth Then
   Response.Write &quot;<td>&quot;&MonthName(month(workingDate))&&quot;</td>&quot;
   curMonth = month(workingDate)
Else
   Response.Write &quot;<td></td>&quot;
End If

'write your other fields
'write your </tr>
'MoveNext on your recordset
Loop

Not to bad, get the year from the record you are woking on. If it is differant than the last year you worked on than create a new year header, other wise leave the first column blank.
Do the same for the month in second column.
Display your other fields.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html

 

[JohannIcon]

Thanks very much Tarwn!