QUERY IN BETWEEN DATES

[kingjjx]

Hi, I am trying to make a search page that would allow the user to query from year X to year Y ... I have 2 drop down boxes that has a selection of years. I want it to query the years in between Year X and Year Y .. how do i write the script for this ???
thank you

-jon

 

[crafter]

SQL provides the BETWEEN condition to test whether a value falls between two other values. Your query would be something like this:

SELECT year
FROM table
WHERE year BETWEEN #Year1# AND #Year2#

Hope that helps.

 

[kingjjx]

<CFQUERY NAME=&quot;Displaylist&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>

HOW DO I ADD ANOTHER CONDITION TO QUERY IN BETWEEN YEAR1 AND YEAR2 ???

THANK YOU

 

[crafter]

Try this:

<CFQUERY NAME=&quot;Displaylist&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>

<cfif IsDefined(&quot;form.SearchYear1&quot AND IsDefined(&quot;form.SearchYear2&quot>
AND RunData.Year BETWEEN #formSearchYear1# AND #form.SearchYear2#)
</cfif>

</cfquery>

 

[kingjjx]

hey, the problem with this tage:

<cfif IsDefined(&quot;form.SearchYear1&quot AND IsDefined(&quot;form.SearchYear2&quot>
AND RunData.Year BETWEEN #formSearchYear1# AND #form.SearchYear2#)
</cfif>


is I have Year1 and Year2 so I can't just use RunData.Year. I have 2 years with different names.

hope you can help
thank you
-jon