SQL Formatting:

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#%'
sjha81Asked:
Who is Participating?
 
dgrafxCommented:
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
 
sjha81Author Commented:
Yeah! no errors.  

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

Am i missing something?
0
 
sjha81Author Commented:
Oh GOT it..  I changed the form name  duh
 
<cfif structkeyexists(form,"spool")>
<cfset pool=1>
<cfelse>
<cfset pool=0>
</cfif>

Thanks!
0
 
dgrafxCommented:
glad I could help and good luck ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.