<% Set objConn = Server.CreateObject("ADODB.Connection") objConn.open Application("ConnectionString") function getval(field, alt) if rs.eof then getval = alt else getval = rs(field) end if end function function getMonthDays(monthVal, yearVal) if monthVal > 12 or monthVal < 1 then getMonthDays = 0 elseif monthVal = 4 OR monthVal = 6 OR monthVal = 9 OR monthVal = 11 then 'april, june, september, and november have 30 days getMonthDays = 30 elseif monthVal = 2 then 'figure out if it is a leap year if yearVal mod 100 = 0 then if yearVal mod 400 = 0 then getMonthDays = 29 else getMonthDays = 28 end if else if yearVal mod 4 = 0 then getMonthDays = 29 else getMonthDays = 28 end if end if else 'all the rest have 31 getMonthDays = 31 end if end function %>

Remaking American Medicine: Health Care for the 21st Century

A public television series and national outreach campaign

www.ramcampaign.org

<% first = true limitYear = " [Date] >= '1/1/" & datepart("yyyy",date()) & "'" Set rs = Server.CreateObject("ADODB.RecordSet") Set rsDates = Server.CreateObject("ADODB.RecordSet") rs.activeconnection = objConn rsDates.activeconnection = objConn 'Build SQL query to get list of all events which match search parameters SQL = "SELECT DISTINCT Events.EventID, Event, Location, Description, MIN([Date]) AS FirstDate, MAX([Date]) AS LastDate" ', EventCategory1.Category AS Category1, EventCategory2.Category AS Category2 if request.form("sortby") = "date" then 'if sorting by date SQL = SQL & ", MONTH(Event_schedule.[Date]) AS MonthIn, YEAR(Event_schedule.[Date]) AS YearIn" end if SQL = SQL & " FROM Event_schedule LEFT JOIN Events ON Events.EventID = Event_schedule.EventID " 'SQL = SQL & "LEFT JOIN EventCategory EventCategory1 ON EventCategory1.ID = Events.Category1 " 'SQL = SQL & "LEFT JOIN EventCategory EventCategory2 ON EventCategory2.ID = Events.Category2 " SQL = SQL & "WHERE " if request.form("keywords") <> "" then 'if any keywords were submitted SQL = SQL & "(" if request.form("kw_type") = "full" then 'if 'full string' search SQL = SQL & " Event LIKE '%" & request.form("keywords") & "%' OR Location LIKE '%" & request.form("keywords") & "%' OR Description LIKE '%" & request.form("keywords") & "%'" else 'if not 'full string' search keywords = split(request.form("keywords")," ") if request.form("kw_type") = "any" then 'if 'any word' search conjunct = "OR" elseif request.form("kw_type") = "all" then 'if 'all words' search conjunct = "AND" end if for x = 0 to ubound(keywords) 'parse keyword string SQL = SQL & " Event + ' ' + Location + ' ' + Description LIKE '%" & keywords(x) & "%'" if x < ubound(keywords) then SQL = SQL & " " & conjunct end if next end if SQL = SQL & ")" end if if request.form("category") <> "" then 'if a category was selected if right(SQL, 6) <> "WHERE " then SQL = SQL & " AND " end if SQL = SQL & "(Events.Category1 = " & request.form("category") & " OR " & "Events.Category2 = " & request.form("category") & ")" end if if request.form("daterange") <> "all" then 'if a specific date range is selected 'if search by month and year is not selected with no month and year selected if not(request.form("daterange") = "monthyear" AND request.form("year") = "0" AND request.form("month") = "0") then if right(SQL, 6) <> "WHERE " then SQL = SQL & " AND " end if if request.form("daterange") = "range" then 'if date range is selected limitYear = "" SQL = SQL & "(Event_schedule.date >= '" & request.form("date1x") & "' AND Event_schedule.date <= '" & request.form("date2x") & "')" elseif request.form("daterange") = "monthyear" then ' if search by month and year is selected limitYear = "" SQL = SQL & "(" if request.form("year") <> "0" then 'if a year is selected SQL = SQL & "DATEPART(yy,Event_schedule.date) = " & request.form("year") if request.form("month") <> "0" then 'if a month and year are selected SQL = SQL & " AND " end if end if if request.form("month") <> "0" then 'if only a month is selected SQL = SQL & "DATEPART(mm,Event_schedule.date) = " & request.form("month") end if SQL = SQL & ")" end if end if end if if limitYear <> "" then if right(SQL, 6) <> "WHERE " then SQL = SQL & " AND " end if SQL = SQL & limitYear end if SQL = SQL & " GROUP BY " if request.form("sortby") = "date" then 'if sorting by date SQL = SQL & "YEAR(Event_schedule.[Date]), MONTH(Event_schedule.[Date]), " end if SQL = SQL & "Events.EventID, Event, Location, Description " ', EventCategory1.Category, EventCategory2.Category 'SQL = SQL & "HAVING (MIN([Date]) >= '" & date() & "' OR MIN([Date]) = MAX([Date])) " SQL = SQL & "ORDER BY " if request.form("sortby") = "title" then 'if sorting by event name SQL = SQL & "Event, FirstDate, Location, Description" elseif request.form("sortby") = "date" then 'if sorting by date SQL = SQL & "FirstDate, Event, Location, Description" end if 'response.write SQL 'Query done rs.open SQL x = 0 'get date and time list for each event do until rs.eof 'cycle through recordset of events generated by above query ID = rs("EventID") 'generate query to get all dates for above events SQL = "SELECT DISTINCT [Date], [Time], EventID FROM Event_schedule WHERE EventID = " & rs("EventID") if limitYear <> "" then SQL = SQL & " AND " & limitYear end if if request.form("sortby") = "date" then 'if sorting by date SQL = SQL + " AND MONTH(Event_schedule.[Date]) = " & rs("MonthIn") & " AND YEAR(Event_schedule.[Date]) = " & rs("YearIn") end if if request.form("daterange") = "range" then 'if limiting search by date range SQL = SQL & " AND Event_schedule.date >= '" & request.form("date1x") & "' AND Event_schedule.date <= '" & request.form("date2x") & "'" elseif request.form("daterange") = "monthyear" then 'if limiting search by year and month if request.form("year") <> "0" then 'if a year was searched on SQL = SQL & " AND DATEPART(yy,Event_schedule.date) = " & request.form("year") end if if request.form("month") <> "0" then 'if a month was searched on SQL = SQL & " AND DATEPART(mm,Event_schedule.date) = " & request.form("month") end if end if SQL = SQL & " ORDER BY Time, Date" rsDates.open SQL 'response.write SQL & "

" if not rsDates.eof then 'display the event if dates were found within the search range 'x = x + 1 'if x = 10 then 'print 'back to top' link every 10 events ' backtotop = "[^] Back To Top" ' x = 0 'else ' backtotop = "" 'end if if request.form("sortby") = "date" then 'if ordering by date, display the month and year 'if request.form("sortby") = "date" then 'response.write monthname(rs("MonthIn")) & ", " & rs("YearIn") 'else ' response.write monthname(month(rsDates("date"))) & ", " & year(rsDates("Date")) 'end if %>

" 'response.write "" & rs("Category1") 'if rs("Category1") <> rs("Category2") then ' response.write ", " & rs("Category2") 'end if 'response.write "" & vbcrlf if trim(rs("Location")) <> "" then response.write "" end if if trim(rs("Description")) <> "" then response.write "" end if response.write "

Event:	<% end if if false then 'admin info %> <%= rs("firstdate") & " " & rs("lastdate") & " " & SQL & " " %> <% end if response.write rs("Event") if false then 'admin info %> <%= SQL %> <% end if%>
Dates/Times:	<% 'parse out dates and times for each event previousdate = null do until rsDates.eof 'cycle through each date firstdate = rsDates("date") lasttime = rsDates("time") lastyear = year(rsDates("date")) thisdate = true if isnull(previousdate) then response.write monthname(month(firstdate)) & " " & day(firstdate) else if month(previousdate) = month(firstdate) AND year(previousdate) = year(firstdate) AND not newtime then response.write ", " else if lastyear <> year(lastdate) then response.write " " & lastyear end if response.write " " & monthname(month(firstdate)) & " " end if response.write day(firstdate) end if do until not thisdate lastdate = rsDates("date") rsDates.movenext if rsDates.eof then thisdate = false else if datediff("d",rsDates("Date"),dateadd("d",1,lastdate)) <> 0 then thisdate = false end if if (lasttime <> rsDates("time")) then thisdate = false end if end if if not thisdate then 'response.write lastdate if firstdate <> lastdate then 'if false then if datediff("d",firstdate,lastdate) = 1 then response.write ", " else response.write " – " end if if month(lastdate) <> month(firstdate) OR year(lastdate) <> year(firstdate) then if lastyear <> year(lastdate) then response.write " " & year(lastdate) end if response.write " " & monthname(month(lastdate)) & " " lastyear = year(lastdate) end if response.write day(lastdate) end if previousdate = lastdate end if newtime = false if trim(lasttime) <> "" then if rsDates.eof or rsDates.bof then response.write " " & datepart("yyyy",firstdate) & " " & replace(lasttime,"\|"," - ") newtime = true else if lasttime <> rsDates("Time") then response.write " " & datepart("yyyy",firstdate) & " " & replace(lasttime,"\|"," - ") newtime = true end if end if end if loop loop response.write "
Location:	" & rs("Location") & "
Description:	" & rs("Description") & "

" response.write "
" & backtotop end if rsDates.close rs.movenext loop %>

<% if rs.bof then response.write "No events were found matching the given parameters
" end if rs.close %>