467,895 Members | 1,384 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,895 developers. It's quick & easy.

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!
Mar 28 '08 #1
  • viewed: 3353
Share:
5 Replies
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
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
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
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
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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Robert Neville | last post: by
reply views Thread by CSDunn | last post: by
7 posts views Thread by damjanu | last post: by
8 posts views Thread by dick | last post: by
2 posts views Thread by Salad | last post: by
11 posts views Thread by Bob | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.