Form values

[tapuwa2002]

I have created form where the values are taken from the form. I still get and an error when I put my form value in the statement. But when I run the statement separately it works but now when I combine it doesn't.

<cfif not isdefined("Form.frmpricerange")> 
    <cfset Form.frmpricerange = "N"> 
</cfif>
<CFQUERY NAME="stuff" DATASOURCE="xxxxxx">
Select * 
FROM (people LEFT JOIN Cities ON City = Cities.palce) LEFT JOIN Check ON people.Code = Check.Code
WHERE (((Check.Date)>=#9/6/2011# And (Check.Date)<>#8/31/2011#)
HAVING (((Deal)=Yes) AND ((Webpage)=1) AND ((Date)>=#9/6/2011#) AND ((Released)=Yes) AND ((People.Price) Between <cfif #Form.pricerange# eq 5000 ><cfoutput>1</cfoutput></cfif> AND 30000))
ORDER BY People.Price_from;
</CFQUERY>
<form method="post" name="frmpricerange">

<select name="pricerange" id="pricerange"  style="height:19px">
				<option value="0">Search By Price Range</option>            
				<option value="5000">0 - 5 000</option>
        		<option value="10000">5 000 -10 000</option>
                <option value="20000">10 000 - 20 000</option>
                <option value="50000">20 000 - 50 000</option>
</select> 
<input name="Check" type="submit" />
</form>

Please help scratching my head :-/

[adrianciocalau]

Can you paste the error message?

[tapuwa2002]

Can you paste the error message?

It shows no message just says page not found I have checked everything the file is sitting on the section.but when I remove the between clause it seems to work
. could there be commas quotes I missed.

[arrgh]

It shows no message just says page not found

If you're using IE, it's probably a badly worded error message. Turn off it's friendly error messages or you won't be able to view the real error http://malektips.com/internet-explorer-8-disable-friendly-error-messages.html

but when I remove the between clause it seems to work

That's because your code isn't generating a valid SQL statement. You're not doing anything when Form.pricerange# is NOT equal to 5000. So your sql is incomplete:

AND ((People.Price) Between {there should be something here})

WHERE (((Check.Date)>=#9/6/2011# And (Check.Date)<>#8/31/2011#)

But overall the query doesn't make sense. There's a lot of contradictory and redundant conditions. Let's step back and figure out what you're trying to do in plain english 1st. Then we can help you put it into SQL.

A few small tips:
- Don't use Access specific syntax for dates ie #9/6/2011# . It's not portable. Better to use cfqueryparam or CreateODBCDate instead.
- A HAVING clause is only needed for aggregates. You're not using any. So all of those conditions belong in the WHERE clause instead

[arrgh]

Use instead. It does the same thing in a lot less code.

<cfparam name="Form.frmpricerange" default="N">