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

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!

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

Quote:

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.

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?

Jim Mandala wrote:

Quote:

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

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" <rickbrandt2@hotmail.comwrote in message
news:3bhHj.226$Gq7.188@newssvr19.news.prodigy.net. ..

Quote:

Jim Mandala wrote:

Quote:

>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
>

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

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

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

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

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

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

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

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about