RETURN IN BETWEEN YEARS QUERY SCRIPT

[kingjjx]

hI, i want to make a query to return the results of all matches that is in between the 2 chosen dates.

HERE IS THE SCRIPT FOR MY SEARCH FORM PAGE:
First Year:
<SELECT name=&quot;SearchYear1&quot;>
<OPTION value=&quot;All&quot;>ALl
<CFOUTPUT QUERY=&quot;Searchdyno&quot;>
<option value=&quot;#FirstYear#&quot;>#FirstYear#</cfoutput>
</select> <br>

Last Year:
<SELECT name=&quot;SearchYear2&quot;>
<OPTION value=&quot;All&quot;>ALl
<CFOUTPUT QUERY=&quot;Searchdyno&quot;>
<option value=&quot;#lastyear#&quot;>#lastyear#</cfoutput>
</select>

How do i write the query script in the cf file to return the result of all matches that are within (between) the first year and 2nd year ??

PLEASE HELP
THANK YOU

 

[alexfusion]

Hi kingjjx:
I think this should solve your problem:

Here I added the name of the CF action page.You put the name you wish:

<form action=&quot;getyears.cfm&quot; method=&quot;post&quot;>
First Year:
<SELECT name=&quot;SearchYear1&quot;>
<OPTION value=&quot;All&quot;>ALl
<CFOUTPUT QUERY=&quot;Searchdyno&quot;>
<option value=&quot;#FirstYear#&quot;>
#FirstYear#
</cfoutput>
</select>
<br>

Last Year:
<SELECT name=&quot;SearchYear2&quot;>
<OPTION value=&quot;All&quot;>ALl
<CFOUTPUT QUERY=&quot;Searchdyno&quot;>
<option value=&quot;#lastyear#&quot;>
#lastyear#
</cfoutput>
</select>
</form>

Here is the code for the action page &quot;getyears.cfm&quot;:


<CFQUERY name=&quot;getyears&quot; datasource=&quot;your_datasource&quot;>
SELECT * FROM your_table_name
WHERE firstyear='#FORM.SearchYear1#' AND lastyear='#FORM.SearchYear2#'
</CFQUERY>

<CFOUPUT QUERY=&quot;getyears&quot;>

Data between #firstyear# and #lastyear#

Put here all of the data you want to show from your database

</CFOUTPUT>

Please note I wrote the SELECT * SQL statement in the query.You may modify this retrieving the field values of the table you want but you must include the fields firstyear and lastyear.

I hope this helps.

alexfusion

 

[kingjjx]

hey ... if i already have these conditions (below) in my action page, how do i add the cfif statements for the years ??
plese help .. thanks

<CFQUERY NAME=&quot;GetResults&quot; DATASOURCE=&quot;DynoRunEx&quot;>
SELECT * FROM RunData WHERE 1 =1
<cfif #form.SearchMake# NEQ &quot;All&quot;>
AND RunData.Make LIKE '%#SearchMake#%'
</cfif>
<cfif #form.SearchModel# NEQ &quot;All&quot;>
AND Rundata.Model LIKE '%#SearchModel#%'
</cfif>


</cfquery>