QUERY SCRIPT HELP !!!

[kingjjx]

Hi ... below is my code for my search page.
I want to add the capability to query between a user selected first year and last year.

example ... all models from 1997 to 2001
right now all it does is query by Make and Model .. but I want to add the years ...

how do i write this script ?



<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>


THANK YOU
-JON

 

[twcman]

Jon,
Try adding the following code to the 'where' section of the query. You will need to values from your search form #year1# and #year2#.

<cfif form.year1 NEQ 'all' AND form.year2 NEQ 'all'>
AND #year1# BETWEEN #year2#


should work. you might need to do some validation to make sure year1 LT year2 etc... also, this is an inclusive comparison.

hope this helps
cnsisa
&quot;the only dumb questions are the ones you do not ask&quot;

 

[twcman]

wait a minute. I have been coding too long today. that should read more like..

AND rundata.year BETWEEN #year1# AND #year2#

sorry.

cnsisa
&quot;the only dumb questions are the ones you do not ask&quot;

 

[kingjjx]

hey .. I added the code you mentioned above ... now it looks like this :

<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>
<cfif #form.Searchfyr# NEQ &quot;all&quot; AND #form.Searchlyr# NEQ &quot;all&quot;>
AND #Firstyear# BETWEEN #lastyear#
</cfif>
</cfquery>


IT GIVES ME THE ERROR SYAING :

An error occurred while evaluating the expression:

#form.Searchfyr# NEQ &quot;all&quot; AND #form.Searchlyr# NEQ &quot;all&quot;


Error resolving parameter FORM.SEARCHFYR

The specified form field cannot be found. This problem is very likely due to the fact that you have misspelled the form field name.

The error occurred while processing an element with a general identifier of (CFIF), occupying document position (9:1) to (9:64).

**********
I CHECKED THE SPELLING AND EVERYTHING AND IT MATCHES WITH THE SEARCH PAGE. HOPE YOU CAN HELP.
THANK YOU