?
Solved

SQL Formatting:

Posted on 2006-11-29
4
Medium Priority
?
210 Views
Last Modified: 2013-12-24
I am working on a search page.  There are form type text, list boxes, and checkboxes.  I was wondering I could get some suggestions on how this code is structured.  I have been testing it out and it seems like the search function works.  

Then I added the boolean types.  The checkboxes seem to not work.  When I check pool or view, it always returns an error

I am using Microsoft Access and made the checkboxes "on/off".  I thought this would work after I tried cfoutputting Form.spool and Form.sview.   They both ouput as "on"

1) How to make the checkboxes work.

2) Suggestions on the structure.

Thanks in advance.

SELECT reals_id, zip.zip, city.city, state.state, price, street, baths, beds, mls, sqft, pool, view
FROM reals, zip, city, state
WHERE (0=0)
      AND mls LIKE '#Session.mls#%'
      AND reals.city = city.city_id
      AND reals.state = state.state_id
      AND reals.zip = zip.zip_id
      AND reals.price >= #Session.minprice#
      AND reals.price <= #Session.maxprice#
      AND city.city LIKE '#Session.scity#%'
      AND state.state LIKE '#Session.sstate#%'
      AND zip.zip LIKE '#Session.szip#%'
      AND sqft >= #Val(Session.ssqft)#
      AND beds LIKE '#Session.sbeds#%'
      AND baths LIKE '#Session.sbaths#%'
      AND pool LIKE '#Session.spool#%'
      AND view LIKE '#Session.sview#%'
0
Comment
Question by:sjha81
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
dgrafx earned 2000 total points
ID: 18035914
on your action page before you run your query do this:
<cfif structkeyexists(form,"pool")>
<cfset pool=1>
<cfelse>
<cfset pool=0>
</cfif>
Do this for any checkbox form fields you have.
then in your query do:
and pool = #pool#
your error cam from the ' ' around a bit column
only use ' ' around text columns
0
 

Author Comment

by:sjha81
ID: 18036011
Yeah! no errors.  

However, in the search page I check pool and view it keeps passing 0

Am i missing something?
0
 

Author Comment

by:sjha81
ID: 18036019
Oh GOT it..  I changed the form name  duh
 
<cfif structkeyexists(form,"spool")>
<cfset pool=1>
<cfelse>
<cfset pool=0>
</cfif>

Thanks!
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 18036023
glad I could help and good luck ...
0

Featured Post

Turn your laptop into a mobile console!

The CV211 Laptop USB Console Adapter provides a direct Laptop-to-Computer connection for fast and easy remote desktop access with no software to install.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month10 days, 2 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question