This makes your query run slow, but it worked for me. Just replace
reportmonth with your select name.
<CFQUERY DATASOURCE="#Application.DSN#" NAME="updateguide">
SELECT eventid, eventdate, longtitle
FROM whatson
Where <cfif reportmonth is 1 OR reportmonth is 3 OR reportmonth is 5 OR
reportmonth is 7 OR reportmonth is 8 OR reportmonth is 10 OR reportmonth is
12>
eventdate between #CreateDate(reportyear, reportmonth, 01)# AND
#CreateDate(reportyear, reportmonth, 31)#
<cfelseif reportmonth is 4 OR reportmonth is 6 OR reportmonth is 9 OR
reportmonth is 11>
eventdate between #CreateDate(reportyear, reportmonth, 01)# AND
#CreateDate(reportyear, reportmonth, 30)#
<cfelseif reportmonth is 2>
<cfif reportyear MOD 4 is 0>
eventdate between #CreateDate(reportyear, reportmonth, 01)#
AND #CreateDate(reportyear, reportmonth, 29)#
<cfelse>
eventdate between #CreateDate(reportyear, reportmonth, 01)#
AND #CreateDate(reportyear, reportmonth, 28)#
</cfif>
</cfif>
ORDER BY eventdate
</CFQUERY>
The only other way I can think of is to put an if statement around the
output <cfif #month(eventdate)# is #selectmonth#>display; but of course that
has the disadvantage of returning all the records you don't need.
-----Original Message-----
From: Ian Vaughan [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 17, 2002 10:29 AM
To: CF-Talk
Subject: Group queries in Coldfusion by date.
Hi
I have a query that brings back entries from my database, so that users can
edit them in their web browser.
At present the query returns all records, but I would like to display the
output generated by date, so for example only January would be shown and if
entries in the database existing for events in February, March etc they
would be accessible via a drop down form select box.
I.E. The user selects April from the select box and the page is refreshed
showing only the entries for April and not January.
Any ideas on how to achieve this? or is it achievable.
This is my current code, I am using an Oracle 8 database
<CFQUERY DATASOURCE="#Application.DSN#" NAME="updateguide">
SELECT eventid, eventdate, longtitle
FROM whatson
ORDER BY eventdate
</CFQUERY>
-----------------
<table width="100%" cellpadding="1" cellspacing="1" border="0" valign="top">
<!--- START --->
<CFOUTPUT QUERY="updateguide">
<tr>
<td width="20%">
<span class="gstext11">#DateFormat(eventdate,"d-mmmm-yyyy")#</span>
</td>
<td width="60%"><span class="gsheader11">#longtitle#</span></td>
<td width="10%" align="center"><a
href="changeeventdetails.cfm?eventid=#eventid#"
class="gsyellow11">EDIT</a></td>
<td width="10%" align="center"><a
href="javascript:confirmDelete('mediadelete.cfm?eventid=#eventid#')"
class="checkprice">DELETE</a></td></tr>
<tr>
<td width="100%" colspan="4" align="center">
<img src="/images/dot_grey.gif"
width="100%" height="1" vspace="2">
</td>
</tr>
</CFOUTPUT>
<!--- END --->
</table>
TIA
Ian
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
