T
Texas Dave
Guest
Thanks in advance to any Guru's out there who can help with this weird one! 
As you know, checkboxes don't have just two values, they actually have THREE values:
1) a blank value, empty. This is the data inside the field for a checkbox that has not been touched AND does not have a default value. When you open a form that contains checkboxes without default value, they are grey and empty.
2) "-1", or "checked"
3) "0", or "unchecked"
My problem:
I have a table called "customers". Inside are about 15 fields for a homemade apartment database. For example, I use checkboxes to make it easy for me to select which amenities a property has. It looks like this:
tennis ( ) pool ( ) hot tub ( ) handicap access ( )
When the user comes in, the checkboxes are grey because I don't want a default value in the field data. Therefore data in the fields are all empty.
The user checks on let's say tennis and pool and hot tub.
tennis (X) pool (X) hot tub (X) handicap access ( )
The field data now looks like this:
tennis pool hottub handicap
-1 -1 -1
In this scenario everything works fine. My matching / search function works fine at this point, chugging through 500 properties. emphasize chugging...
The problem exists when the user says, hmmm, maybe I don't need a hot tub, and then unchecks the box... like so:
tennis (X) pool (X) hot tub ( ) handicap access ( )
CRIKEY!!!
The field data now looks like this:
tennis pool hottub handicap
-1 -1 0
Now my matching function not only finds properties with tennis courts and pools, it also STILL finds them with hot tubs because the field is 'unchecked' and not 'empty'.
Trust me, if I could change the search function I would, but the property profile data is input USING CHECKBOXES... When I create a new property, I don't sit there and type -1 or 0's into text boxes. I use checkboxes to create the profile. I want to use checkboxes to search the profiles as well. Seems pretty common-sensicle right??? hmmm...
Well. My solution leads to a huge problem that I've found...
I want to do the following:
CLEAN OUT THE 0'S FROM MY CUSTOMER TABLE....
What I'm saying in semi-SQL speak is:
update ALL instances of "0" with "" in ALL fields in the customer table
problem is that as you know, you can only update one field at a time... and I have 15 fields each time that must be scanned prior to searching to clean out those mysterious 0's.
Updating one field is easy, I've been able to do that using a SQL replace command. but I can't seem to figure out how to simply update all the fields at once using one SQL statement... I can't imagine that something so common is so hard to do.
I've seen lots of amazing answers on this board, by far you guys are the experts, hopefully you could shine some light on my dilemna here and rescue me from staying indoors wrapped around this damn machine...!!!
thanks from Texas!
dave
As you know, checkboxes don't have just two values, they actually have THREE values:
1) a blank value, empty. This is the data inside the field for a checkbox that has not been touched AND does not have a default value. When you open a form that contains checkboxes without default value, they are grey and empty.
2) "-1", or "checked"
3) "0", or "unchecked"
My problem:
I have a table called "customers". Inside are about 15 fields for a homemade apartment database. For example, I use checkboxes to make it easy for me to select which amenities a property has. It looks like this:
tennis ( ) pool ( ) hot tub ( ) handicap access ( )
When the user comes in, the checkboxes are grey because I don't want a default value in the field data. Therefore data in the fields are all empty.
The user checks on let's say tennis and pool and hot tub.
tennis (X) pool (X) hot tub (X) handicap access ( )
The field data now looks like this:
tennis pool hottub handicap
-1 -1 -1
In this scenario everything works fine. My matching / search function works fine at this point, chugging through 500 properties. emphasize chugging...
The problem exists when the user says, hmmm, maybe I don't need a hot tub, and then unchecks the box... like so:
tennis (X) pool (X) hot tub ( ) handicap access ( )
CRIKEY!!!
The field data now looks like this:tennis pool hottub handicap
-1 -1 0
Now my matching function not only finds properties with tennis courts and pools, it also STILL finds them with hot tubs because the field is 'unchecked' and not 'empty'.
Trust me, if I could change the search function I would, but the property profile data is input USING CHECKBOXES... When I create a new property, I don't sit there and type -1 or 0's into text boxes. I use checkboxes to create the profile. I want to use checkboxes to search the profiles as well. Seems pretty common-sensicle right??? hmmm...
Well. My solution leads to a huge problem that I've found...
I want to do the following:
CLEAN OUT THE 0'S FROM MY CUSTOMER TABLE....
What I'm saying in semi-SQL speak is:
update ALL instances of "0" with "" in ALL fields in the customer table
problem is that as you know, you can only update one field at a time... and I have 15 fields each time that must be scanned prior to searching to clean out those mysterious 0's.
Updating one field is easy, I've been able to do that using a SQL replace command. but I can't seem to figure out how to simply update all the fields at once using one SQL statement... I can't imagine that something so common is so hard to do.
I've seen lots of amazing answers on this board, by far you guys are the experts, hopefully you could shine some light on my dilemna here and rescue me from staying indoors wrapped around this damn machine...!!!
thanks from Texas!
dave
