data search based on search criteria

[potinenip]

Hi<br>Can somebody help me with this. I am trying to get data into tables based on search criteria given in form. My form has 5 variables like state, bedrooms, bathrooms, property type etc..<br>I should be able to retrieve only those records whose state is texas and has 2 bedrooms and 3 bathrooms and property type is House.<br>I hope i made myself clear. <br>Please help me on this.

 

[DarkMan]

Most people looking for real estate want at least X bedrooms and X bathrooms, so this will return houses with at least as many bedrooms and bathrooms as they have specified..<br><br><FONT FACE=monospace><b>&lt;cfparam name=&quot;State&quot; default=&quot;&quot;&gt;<br>&lt;cfparam name=&quot;Bedrooms&quot; default=&quot;0&quot;&gt;<br>&lt;cfparam name=&quot;Bathrooms&quot; default=&quot;0&quot;&gt;<br>&lt;cfparam name=&quot;PropertyType&quot; default=&quot;&quot;&gt;<br><br>&lt;cfquery name=&quot;results&quot; datasource=&quot;mydb&quot;&gt;<br>&nbsp;&nbsp;SELECT * from Homes<br>&nbsp;&nbsp;WHERE Bedrooms &gt;= #Bedrooms# and<br>&nbsp;&nbsp;Bathrooms &gt;= #Bathrooms#<br>&nbsp;&nbsp;&lt;cfif len(State)&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;and State = '#State#'<br>&nbsp;&nbsp;&lt;/cfif&gt;<br>&nbsp;&nbsp;&lt;cfif len(PropertyType)&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;and PropertyType = '#PropertyType#'<br>&nbsp;&nbsp;&lt;/cfif&gt;<br>&lt;/cfquery&gt;</b></font><br><br>If you want to return exactly as many as they have specified do something like:<br><br><FONT FACE=monospace><b>&lt;cfparam name=&quot;State&quot; default=&quot;&quot;&gt;<br>&lt;cfparam name=&quot;Bedrooms&quot; default=&quot;0&quot;&gt;<br>&lt;cfparam name=&quot;Bathrooms&quot; default=&quot;0&quot;&gt;<br>&lt;cfparam name=&quot;PropertyType&quot; default=&quot;&quot;&gt;<br><br>&lt;cfquery name=&quot;results&quot; datasource=&quot;mydb&quot;&gt;<br>&nbsp;&nbsp;SELECT * from Homes<br>&nbsp;&nbsp;WHERE 1=1<br>&nbsp;&nbsp;&lt;cfif Bedrooms&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;and Bedrooms &gt;= #Bedrooms#<br>&nbsp;&nbsp;&lt;/cfif&gt;<br>&nbsp;&nbsp;&lt;cfif Bathrooms&gt;<br>&nbsp;&nbsp;&nbsp;and Bathrooms &gt;= #Bathrooms#<br>&nbsp;&nbsp;&lt;/cfif&gt;<br>&nbsp;&nbsp;&lt;cfif len(State)&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;and State = '#State#'<br>&nbsp;&nbsp;&lt;/cfif&gt;<br>&nbsp;&nbsp;&lt;cfif len(PropertyType)&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;and PropertyType = '#PropertyType#'<br>&nbsp;&nbsp;&lt;/cfif&gt;<br>&lt;/cfquery&gt;</b></font><br><br>both of these queries will leave out criteria if it isn't specified... (example: if they haven't specified a state, it will return all matches regardless of which state it's located in.)&nbsp;&nbsp;If you're forcing entries in all fields, you don't need the CFIFs.<br><br><FONT FACE=monospace><b><br>&lt;cfquery name=&quot;results&quot; datasource=&quot;mydb&quot;&gt;<br>&nbsp;&nbsp;SELECT * from Homes<br>&nbsp;&nbsp;WHERE Bedrooms &gt;= #Bedrooms# and<br>&nbsp;&nbsp;Bathrooms &gt;= #Bathrooms#<br>&nbsp;&nbsp;and State = '#State#'<br>&nbsp;&nbsp;and PropertyType = '#PropertyType#'<br>&lt;/cfquery&gt;</b></font><br><br>Hope this helps....<br><br>DM<br>

 

[potinenip]

DM,<br><br>This is the error i got. <br><br>ODBC Error Code = 22005 (Error in assignment)<br>[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.<br><br>This is how i wrote my code and i don't know where i might be wrong.<br>&lt;cfquery name=&quot;Homes&quot; datasource=&quot;db1&quot;&gt;<br>&nbsp;&nbsp;SELECT * from Property<br>&nbsp;&nbsp;WHERE PropBedrooms &gt;= #PropBedrooms# and<br>&nbsp;&nbsp;PropBathrooms &gt;= #PropBathrooms#<br>&nbsp;&nbsp;and PropState = '#PropState#'<br>&nbsp;&nbsp;and PropType = '#PropType#'<br>&nbsp;&nbsp;and PropPrice &lt;= #MaxPrice# <br>&lt;/cfquery&gt;

 

[DarkMan]

Check my answer in your other post &quot;To DarkMan&quot;...&nbsp;&nbsp;This error says that you are either using single quotes where you shouldn't or not using them where you should...&nbsp;&nbsp;Check your datatypes in your table....