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!

[lyle]

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

On Mar 28, 10:40 am, Jim Mandala <mand...@rci.ru tgers.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.

[Jim Mandala]

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

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?

[Rick Brandt]

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

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

[Larry Linson]

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

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@ho tmail.comwrote in message
news:3bhHj.226$ Gq7.188@newssvr 19.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
>

[Jim Mandala]

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

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