I did this yesterday, im not sure if it would work w/ access or not. but
maybe:
==my Form==
<!--- query to get dates from database --->
<cfquery name="GetDates"
datasource="#datasource#"
username="#username#"
password="#password#"
dbtype="ODBC">
SELECT DateEntered
FROM WorkOrderManager
ORDER BY DateEntered
</cfquery>
<!--- Form --->
<cfset LoopDate = 0>
<tr>
<td colspan="3"><font face="verdana" size="1">Sort By Date Range:
</font><form action="dateRange.cfm"><select name="dateRange1"><option>From
Date<cfoutput query="GetDates"><cfif GetDates.DateEntered NEQ
LoopDate><option
value="#UrlEncodedFormat(DateFormat(DateEntered,"mm/dd/yyyy"))#">#DateFormat
(DateEntered,"mm/dd/yyyy")#</cfif><cfset LoopDate =
DateEntered></cfoutput></select> <select name="dateRange2"><option>To
Date<cfoutput query="GetDates"><cfif GetDates.DateEntered NEQ
LoopDate><option
value="#UrlEncodedFormat(DateFormat(DateEntered,"mm/dd/yyyy"))#">#DateFormat
(DateEntered,"mm/dd/yyyy")#</cfif><cfset LoopDate =
DateEntered></cfoutput></select><input type="Submit" value="Go"></form>
</td>
==query in dateRange.cfm==
<cfquery name="GetWorkOrders"
datasource="#datasource#"
username="#username#"
password="#password#"
dbtype="ODBC">
SELECT *
FROM WorkOrderManager
WHERE DateEntered >= '#urlDecode(url.dateRange1)#' AND DateEntered <=
'#urlDecode(url.dateRange2)#'
ORDER BY DateEntered
</cfquery>
Hope this helps!
Jay Patton
Web Design / Application Design
Web Pro USA
p. 406.549.3337 ext. 203
p. 1.888.5WEBPRO ext. 203
e. [EMAIL PROTECTED]
url. www.webpro-usa.com
----- Original Message -----
From: "Robert Orlini" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, April 04, 2001 9:54 AM
Subject: RE: date range search
> Thanks for the suggestion. It didn't work though. I have a form with a
> dropdown menu. Can dates in an Access table between: 01/01/00 and 01/01/01
> be searched ?
> -------------------------------------
> Using my code:
> <CFSET startdate = "#Createdate(Year,Month,Day)#">
> <CFSET enddate = "#Createdate(Year2,Month2,Day2)#">
> <cfset startdate = #dateformat(startdate,'mm/dd/yy')#>
> <cfset enddate = #dateformat(enddate,'mm/dd/yy')#>
>
> <CFQUERY name="getwweb" datasource="wwebsales">
> SELECT * FROM wwebsales
> where orderdate between #startdate# and #enddate#
> </cfquery>
> -----------------------------------
> I can the following error:
>
> ODBC Error Code = 22012 (Division by zero)
> [Microsoft][ODBC Microsoft Access Driver]Division by zero (null)
> SQL = "SELECT * FROM wwebsales where orderdate between 01/01/00 and
> 01/01/01"
> Data Source = "wwebsales"
> ----------------------------------
>
> Thanks.
>
> Robert O.
>
> >-----Original Message-----
> >From: Lamon, Alec [mailto:[EMAIL PROTECTED]]
> >Sent: Tuesday, April 03, 2001 3:04 PM
> >To: CF-Talk
> >Subject: RE: date range search
> >
> >
> >I think you want to use the CreateODBCDate() function to create
> >your start and end dates.
> ><CFSET startdate = "#CreateODBCdate(Year,Month,Day)#">
> ><CFSET enddate = "#CreateODBCdate(Year2,Month2,Day2)#">
> >
> >Then refer to those in your query *without* 's. (the
> >CreateODBCDate() function puts them in for you):
> >
> >
> ><CFQUERY name="getwweb" datasource="wwebsales">
> >SELECT * FROM wwebsales
> >where orderdate between #startdate# and #enddate#
> ></cfquery>
> >
> >> -----Original Message-----
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
