Filter by form problem with Yes/No Data and SQL Server

Jim Mandala

Using Access 2003 front end; SQL Server 2005 Back end:

I have a complex form that has lots of data fields including about
thirty or so checkboxes storing Yes/No data that I would like my users
to be able to use the Filter by Form functionality with to create a
"Custom" query.

This works perfectly fine when I filter on a text field. Also, this
works perfectly fine for all data with an Access Back end. When I use
boolean data and my SQL server back end, however, the filter stops
working. I understand that the SQL server stores the Yes as "1" while
Access, and my "Filter Form" uses "-1" for Yes.

Clicking on a box in the Filter by Form creates a Me.Filter string
which looks like "CheckBox7 = -1". I don't really understand this,
since the checkbox on the actual Form stores a value of 1 in the SQL
table.

Is there a way I can get the "Filter By Form" to work with Boolean
Data?

Thanks!

Mar 28 '08 #1

lyle

On Mar 28, 10:40*am, Jim Mandala <mand...@rci.rutgers.eduwrote:

Using Access 2003 front end; SQL Server 2005 Back end:

I have a complex form that has lots of data fields including about
thirty or so checkboxes storing Yes/No data that I would like my users
to be able to use the Filter by Form functionality with to create a
"Custom" query.

This works perfectly fine when I filter on a text field. Also, this
works perfectly fine for all data with an Access Back end. When I use
boolean data and my SQL server back end, however, the filter stops
working. I understand that the SQL server stores the Yes as "1" while
Access, and my "Filter Form" uses "-1" for Yes.

Clicking on a box in the Filter by Form creates a Me.Filter string
which looks like "CheckBox7 = -1". I don't really understand this,
since the checkbox on the actual Form stores a value of 1 in the SQL
table.

Is there a way I can get the "Filter By Form" to work with Boolean
Data?

Thanks!

SQL Server Bit fields are 0 or 1. (I suppose anything bit is 0 or 1
for that matter.) Is that extended to your checkbox on your form?
Don't know but you might try it.

Mar 28 '08 #2

Jim Mandala

Thanks for the reply. I forgot to mention that I am using ODBC to
connect to the SQL server.

The Checkboxes on the normal Form map to 0's and 1's in the data
table. I.e. True = 1. However, when I use the Access' "Filter By
Form" function, it seems to open a 'copy' of the form. The Checkboxes
on this form automatically create a filter, but every checked Checkbox
creates a True = -1; which is the Access standard for True/False data.

Two different data standards... Same Microsoft... Does Bill Gates have
too much money?

Mar 28 '08 #3

Rick Brandt

Jim Mandala wrote:

Thanks for the reply. I forgot to mention that I am using ODBC to
connect to the SQL server.

The Checkboxes on the normal Form map to 0's and 1's in the data
table. I.e. True = 1. However, when I use the Access' "Filter By
Form" function, it seems to open a 'copy' of the form. The Checkboxes
on this form automatically create a filter, but every checked Checkbox
creates a True = -1; which is the Access standard for True/False data.

Two different data standards... Same Microsoft... Does Bill Gates have
too much money?

Yep, I've seen this issue and not just in filter-by-form. The only 100%
reliable tests for a bit field from Access is =0 and <>0. Testing for -1
often does not work and testing for 1 often does not work. Yes/No,
True/False, etc., are equally problematic.

When testing a CheckBox *control* bound to a bit field you can usually use
any of the normal tests as you would with an Access table, but when testing
the actual field value just stick with =0 or <>0. They always work.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Mar 29 '08 #4

Larry Linson

As the value for False in both cases is 0, structuring your test to use Not
False or <False works well. That, of course, is simply another way of
specifying <0, because the value of the builtin Constant False is 0.

Larry Linson
Microsoft Office Access MVP

"Rick Brandt" <ri*********@hotmail.comwrote in message
news:3b***************@newssvr19.news.prodigy.net. ..

Jim Mandala wrote:

>Thanks for the reply. I forgot to mention that I am using ODBC to
connect to the SQL server.

The Checkboxes on the normal Form map to 0's and 1's in the data
table. I.e. True = 1. However, when I use the Access' "Filter By
Form" function, it seems to open a 'copy' of the form. The Checkboxes
on this form automatically create a filter, but every checked Checkbox
creates a True = -1; which is the Access standard for True/False data.

Two different data standards... Same Microsoft... Does Bill Gates have
too much money?

Yep, I've seen this issue and not just in filter-by-form. The only 100%
reliable tests for a bit field from Access is =0 and <>0. Testing for -1
often does not work and testing for 1 often does not work. Yes/No,
True/False, etc., are equally problematic.

When testing a CheckBox *control* bound to a bit field you can usually use
any of the normal tests as you would with an Access table, but when
testing the actual field value just stick with =0 or <>0. They always
work.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Mar 29 '08 #5

Jim Mandala

It looks like I need to completely avoid the Filter by Form function
for my end users to do any searching.

Apr 5 '08 #6