Listing by date

golden_drifter · June 30th, 2008, 08:17 AM

Hi all, hope this is in the correct section.

Im working with an access database doing my webpages with asp vbscript.

I want to list event records by date and have them in sections of months
i.e:
June
30th - Monthly meeting

August
2nd - Trip of Haydock races
28th - Monthly meeting

The records in an access database are like this:
ID* - autonumber
eventDate - date/time (short date)
eventText - memo (the description that is listed next to the date)

The trouble im having is that the records have the date in short date and i'd like to take the data from the database then list the month and then the events in that month underneath.

Is there anyway of doing this at all? I hope i've explained this ok, i'm abit confused :( Any help would be much appreciated.

Thanks
Paul

Old Pedant · June 30th, 2008, 02:10 PM

Let's start with the very wrong assumption you made:

You wrote "the records have the date in short date...".

No, they don't. "short date" is only the DISPLAY FORMAT used WITHIN ACCESS itself. Has NOTHING to do with what is stored in the DB. Here, read this:
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=189

So all you need to do is get the events, in date order, nothing more:

Code:

<%
SQL = "SELECT eventDate, eventText FROM events ORDER BY eventDate " _
    & " WHERE DateValue(eventDate) BETWEEN #1/1/2008# AND #12/31/2008# "
Set RS = yourAlreadyOpenConnectionObject.Execute( SQL )
...
%>

You can change the date range (the BETWEEN values) to whatever you need, of course.

And then the rest of it--grouping by months and days--you can do by following the principals of this:
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=154

Where here the MONTH is the category and the individual days in the month (along with the text, of course) are the subcategories.

golden_drifter · June 30th, 2008, 02:33 PM

Ok thanks for that, i'll have a look and give it a go.

Thanks again :)

Old Pedant · June 30th, 2008, 02:59 PM

Here's a nudge in the right direction, in case you have problems with the category/subcat stuff.

Code:

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source=" & Server.MapPath("...relative path do MDB file...") & ";" 

SQL = "SELECT eventDate, eventText FROM events ORDER BY eventDate " _
    & " WHERE DateValue(eventDate) BETWEEN #1/1/2008# AND #12/31/2008# "

Set RS = conn.Execute( SQL )
If RS.EOF Then
     Response.Write "No events in that date range."
     RS.Close : conn.Close
     Response.End
End If

curYear = 0

Do Until RS.EOF
    eDate = RS("eventDate")
    eText = RS("eventText")
    If curYear <> Year(eDate) Then
        curYear = Year(eDate)
        Response.Write "<span class=YEAR>" & curYear & "</span><br/>" & vbNewLine
        curMonth = 0
    End If
    If curMonth <> Month(eDate) Then
        curMonth = Month(eDate)
        Response.Write "<span class=MONTH>" & curMonth & "</span><br/>" & vbNewLine
    End If
    ' then always output the event itself
    eDay = Day(eDate)
    Select Case eDay
        Case 1, 21, 31 : suffix = "st"
        Case 2, 22     : suffix = "nd"
        Case 3, 23     : suffix = "rd"
        Case Else      : suffix = "th"
    End Select
    Response.Write "<span class=EVENT>" & eDay & suffix & " -- " & eText & "</span><br/>" & vbNewLine
    RS.MoveNext
Loop
%>

Old Pedant · June 30th, 2008, 03:01 PM

Oops...minor goof...

This line

Code:

Response.Write "<span class=MONTH>" & curMonth & "</span><br/>" & vbNewLine

Needs to be

Code:

Response.Write "<span class=MONTH>" & MonthName(curMonth) & "</span><br/>" & vbNewLine

so you get the month as its name, not just its number

golden_drifter · June 30th, 2008, 03:16 PM

I really appreciate the help. :)

I've got the jist of what to do now, i think i just needed a nudge in the right direction! ;)

Thankyou again for the help