Problem With Bit Fields When Running Against SQL 2005

[Neil]

This is the part of your story I don't understand. I first had bit
problems with Access/SQL Server back when we were using Access 97 and SQL
Server 6.5. In my experience the issue you are complaining about has
*always* been that way. The only difference was when SQL Server 2000
started allowing bit fields to be nullable which caused more (different)
problems with Access.

That's very interesting. I'm aware of the Nullable issue, and none of my bit
fields have ever been Nullable. But that's interesting. I know for a fact
that this happened when we upgraded to SQL 2005. And it wasn't just the
upgrade. Initially the database was in SQL 7 compatibility mode (level 70).
Only when we changed the compatibility mode to 2005 (level 90) did this
problem start.

Again, it makes no sense, since the front end is what's doing the
translating. So why should the change to the back end affect Access from
translating -1 into 1. But such is the case.

I even went back to Access 2000 and tried it there, just to confirm that it
wasn't a change to Access that was causing it. And, sure enough, the code
that worked for years failed when used against the SQL 2005 database,
because -1 was being sent as -1, and was not being translated into 1.

The only way I can think to explain this phenomenon would be that perhaps
Access was never converting the -1 to 1 when I did "...where
SomeBitField=-1", but something in SQL Server did. That is, perhaps SQL
Server's engine said, "Hey, when we get junk Where clauses that use -1, just
treat that as though it were a 1 instead." But then, in SQL 2005, the engine
stopped doing that. That's the only explanation I can think of, since
nothing changed on the Access end.

Neil

 

[a a r o n . k e m p f]

the thing that I don't like =0 or <> 0 is I thnk that would effect
indexing.

When I'm converting, I usually change the bit from Jet into a smallint
or tinyint so I can index it.

 

[Albert D. Kallal]

Yes; but the point being that in this case pre-processing wasn't needed.

Previously Access/ODBC took the native -1, saw it as "True", and sent it to
SQL as 1 ("True" in SQL).


Take heed....I am going to bring this issue up with the access team when I
get a chance. I do think this represents a problem. I not going to hold my
breath on this, but I really am on your side here.

I *do* agree this should NOT have been broken, and furthermore, I also think
that jet (or who ever messed this up) should make this issue transparent for
access developers....

 

[Neil]

Previously Access/ODBC took the native -1, saw it as "True", and sent it
to
SQL as 1 ("True" in SQL).


Take heed....I am going to bring this issue up with the access team when I
get a chance. I do think this represents a problem. I not going to hold my
breath on this, but I really am on your side here.

Great. Thanks!

I *do* agree this should NOT have been broken, and furthermore, I also
think that jet (or who ever messed this up) should make this issue
transparent for access developers....

Yes. Something to consider, though, is, since Access was previously doing
the translating; and since nothing changed on the Access end (since this
happened when I upgraded SQL, and I even went back to A2K and tested it
there, with the same results); how did this problem manifest? See my reply
to Rick Brandt in this thread from a little earlier today. Is it possible
that the translating in this scenario was actually happening on the SQL end,
which is why it broke when the SQL engine was upgraded? Something to
consider.

In any case, apart from how code should be written (and thank you, everyone
for all the tips; I've taken them to heart); the fact remains that you have
the weird situation where you can go into query designer, designate -1 as a
criterion for a bit field and get no results; and then change the criterion
to 1 to get results which display -1! Extremely disconcerting.

And...... here's the real kicker!......

For the heck of it, I played around with filtering, and, get this: you bring
up a bunch of records with bit fields. Some of the records display -1 in the
field; others display 0. You click in the bit field for one of the records
that are displaying -1 and click the Filter By Selection button. What
happens? Result set is NO RECORDS!! You filtered a resultset based on a
value that was displayed in the resultset, and you ended up with nothing.
Because it used the display value of -1!

That is what I call a broken system.

Blame it on Access, blame it on SQL, blame it on bit fields; the bottom line
is that this crap's broken. And it previously worked. So why, some 3+ years
after SQL Server 2005 came out, is there still no fix?

I'm astounded that this seems to be the first that many are hearing of this,
when SQL 2005's been out for years. Surely people have run queries against
2005 before and have tried to filter results by a bit field. I don't believe
that I'm the first to do that against 2005. Bizarre.

Thanks!

Neil

 

[Sylvain Lafontaine]

Not tinyint but small integer. Like the Bit field, the tinyint is an
unsigned quantity on SQL-Server and cannot be used to store the value of -1.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

That's a good point about bits not being portable. Using tinyint certainly
would have avoided this problem. I wonder what is the upside to using bit
instead of tinyint? Just being able to do boolean comparisons?

Neil

It's not a glitch, it's a fundamental problem with the translation
between VB (8086) Booleans and SQL Server (PDP) bit fields.

I recommend Never Use Bit Fields, because bit fields aren't really
portable. Use small integer fields instead of bit fields. This is not
just VB and SQL Server: bit fields aren't portable even in C.

Of course, bit fields are normally portable if you only use the zero
value: from "Writing Solid Code": "the portable range of a bit field is
0",
but bit fields are still a problem waiting to happen.

So you can write:

strSQL = strSQL & " AND [Field1]=" & Me.Field1CheckBox

strSQL = strSQL & " AND NOT ([Field1]<>" & Me.Field1CheckBox

but unless I was having trouble with database size I wouldn't do that.

By the way, dates have the same problem as Booleans: if you
use a numeric representation for a date, Access will translate for
SQL Server when it recognises a translation is required: if you
put in date 1 you won't have a record with date 1.

(david)

Not quite that simple. For example, I have a form in one place that has
a series of check boxes that represent bit fields. If the user wants to
return records for which that field is true, they check the box. The
code looks something like:

strSQL = strSQL & " AND [Field1]=" & Me.Field1CheckBox

Thus, it takes the value of the check box and uses it in the dynamic
SQL. The values are -1 and 0, which the ODBC driver sent to SQL Server
as True and False. That code would have to be entirely rewritten. (I've
created a wraparound function that converts the checkbox 0 and -1 to 0
and 1; but it'll have to be applied to the entire database, which is a
ton of code.)

The bottom line here, in my opinion, is that the ODBC driver continues
to DISPLAY -1 for True for data retrieved FROM SQL Server, but requires
us to SEND 1 for True for criteria sent TO SQL Server. That makes no
sense, and seems to be a glitch. I was hoping there was some sort of
patch or fix.

Neil


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message Instead of testing for 1 or -1, you should test for <> 0; this way, you
won't have to chase a moving target.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Have an Access MDB (2002/3 format) running against a SQL Server back
end, using ODBC linked tables. Recently upgraded from SQL 7 to SQL
2005.

With the bit fields in SQL Server, even though their values are 0 and
1 for False and True, the ODBC driver displays them as 0 and -1, which
are the Access False and True values. That was true for SQL 7 as well
as for SQL 2005.

However, I find that when I run a query (not a pass-through; just a
regular Access query), I have to use "1" in the Where clause to get
True values from the SQL table, even though they are displayed as -1!

For example, "Select * From MyTable Where SomeBitField=-1" returns no
records; but "Select * From MyTable Where SomeBitField=1" returns
records. However, the records show -1 in the bit field!!!!

If I use True instead of 1 or -1 it works fine. But I have tons of
code that build dynamic SQL that use -1 for true, and I'd have to
change all of those.

Also, since the above is true for saved queries as well as dynamic
SQL, it seems to be some sort of glitch (since the saved query would
have to have "1" in the Criteria field, but would display "-1" in the
results!).

Does anyone know if this is a known problem and if Microsoft released
a patch or whatever for this? I'm running Access 2003, SP3 (v.
11.8166.8221).

Thanks!

Neil

 

[Rick Brandt]

For the heck of it, I played around with filtering, and, get this: you
bring up a bunch of records with bit fields. Some of the records display
-1 in the field; others display 0. You click in the bit field for one of
the records that are displaying -1 and click the Filter By Selection
button. What happens? Result set is NO RECORDS!! You filtered a
resultset based on a value that was displayed in the resultset, and you
ended up with nothing. Because it used the display value of -1!

That is what I call a broken system.

And again I saw that exact behavior many years ago. It has always
behaved that way in my experience.

 

[david]

Well, bit fields are smaller.

You /can/ do a Boolean comparison on an integer field,
and bind a check box if you want, but again, if you don't
want to depend on the underlying representation of True,
you are better off checking against False.

(david)

That's a good point about bits not being portable. Using tinyint certainly
would have avoided this problem. I wonder what is the upside to using bit
instead of tinyint? Just being able to do boolean comparisons?

Neil

It's not a glitch, it's a fundamental problem with the translation
between VB (8086) Booleans and SQL Server (PDP) bit fields.

I recommend Never Use Bit Fields, because bit fields aren't really
portable. Use small integer fields instead of bit fields. This is not
just VB and SQL Server: bit fields aren't portable even in C.

Of course, bit fields are normally portable if you only use the zero
value: from "Writing Solid Code": "the portable range of a bit field is
0",
but bit fields are still a problem waiting to happen.

So you can write:

strSQL = strSQL & " AND [Field1]=" & Me.Field1CheckBox

strSQL = strSQL & " AND NOT ([Field1]<>" & Me.Field1CheckBox

but unless I was having trouble with database size I wouldn't do that.

By the way, dates have the same problem as Booleans: if you
use a numeric representation for a date, Access will translate for
SQL Server when it recognises a translation is required: if you
put in date 1 you won't have a record with date 1.

(david)

Not quite that simple. For example, I have a form in one place that has
a series of check boxes that represent bit fields. If the user wants to
return records for which that field is true, they check the box. The
code looks something like:

strSQL = strSQL & " AND [Field1]=" & Me.Field1CheckBox

Thus, it takes the value of the check box and uses it in the dynamic
SQL. The values are -1 and 0, which the ODBC driver sent to SQL Server
as True and False. That code would have to be entirely rewritten. (I've
created a wraparound function that converts the checkbox 0 and -1 to 0
and 1; but it'll have to be applied to the entire database, which is a
ton of code.)

The bottom line here, in my opinion, is that the ODBC driver continues
to DISPLAY -1 for True for data retrieved FROM SQL Server, but requires
us to SEND 1 for True for criteria sent TO SQL Server. That makes no
sense, and seems to be a glitch. I was hoping there was some sort of
patch or fix.

Neil


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message Instead of testing for 1 or -1, you should test for <> 0; this way, you
won't have to chase a moving target.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Have an Access MDB (2002/3 format) running against a SQL Server back
end, using ODBC linked tables. Recently upgraded from SQL 7 to SQL
2005.

With the bit fields in SQL Server, even though their values are 0 and
1 for False and True, the ODBC driver displays them as 0 and -1, which
are the Access False and True values. That was true for SQL 7 as well
as for SQL 2005.

However, I find that when I run a query (not a pass-through; just a
regular Access query), I have to use "1" in the Where clause to get
True values from the SQL table, even though they are displayed as -1!

For example, "Select * From MyTable Where SomeBitField=-1" returns no
records; but "Select * From MyTable Where SomeBitField=1" returns
records. However, the records show -1 in the bit field!!!!

If I use True instead of 1 or -1 it works fine. But I have tons of
code that build dynamic SQL that use -1 for true, and I'd have to
change all of those.

Also, since the above is true for saved queries as well as dynamic
SQL, it seems to be some sort of glitch (since the saved query would
have to have "1" in the Criteria field, but would display "-1" in the
results!).

Does anyone know if this is a known problem and if Microsoft released
a patch or whatever for this? I'm running Access 2003, SP3 (v.
11.8166.8221).

Thanks!

Neil

 

[david]

Looking at the ODBC spec, I see that
"The SQL_BIT data type has different characteristics than the BIT type in
SQL-92."
It doesn't say what that means.

If this change was to align MS ODBC with the ODBC 3.x standard, it won't
be changed back - any fix will have to be in Access. (Actually, they
wouldn't
change anything in SQL Server for Access anyway, so I guess we knew that
already.)

Since, to my despair, they've never fixed any SQL Server/Access
compatibility
problems anywhere, I won't hold my breath for this either.

There is no ODBC Boolean type, and the ODBC bit type is unsigned char --
so if you are using ODBC, bits get translated to chars, then Booleans.

(david)

This is the part of your story I don't understand. I first had bit
problems with Access/SQL Server back when we were using Access 97 and SQL
Server 6.5. In my experience the issue you are complaining about has
*always* been that way. The only difference was when SQL Server 2000
started allowing bit fields to be nullable which caused more (different)
problems with Access.

That's very interesting. I'm aware of the Nullable issue, and none of my
bit fields have ever been Nullable. But that's interesting. I know for a
fact that this happened when we upgraded to SQL 2005. And it wasn't just
the upgrade. Initially the database was in SQL 7 compatibility mode (level
70). Only when we changed the compatibility mode to 2005 (level 90) did
this problem start.

Again, it makes no sense, since the front end is what's doing the
translating. So why should the change to the back end affect Access from
translating -1 into 1. But such is the case.

I even went back to Access 2000 and tried it there, just to confirm that
it wasn't a change to Access that was causing it. And, sure enough, the
code that worked for years failed when used against the SQL 2005 database,
because -1 was being sent as -1, and was not being translated into 1.

The only way I can think to explain this phenomenon would be that perhaps
Access was never converting the -1 to 1 when I did "...where
SomeBitField=-1", but something in SQL Server did. That is, perhaps SQL
Server's engine said, "Hey, when we get junk Where clauses that use -1,
just treat that as though it were a 1 instead." But then, in SQL 2005, the
engine stopped doing that. That's the only explanation I can think of,
since nothing changed on the Access end.

Neil

 

[Neil]

And again I saw that exact behavior many years ago. It has always
behaved that way in my experience.

And again, this behavior didn't start until we upgraded to SQL 2005.

For the sake of being sure, I went to our old server, still running SQL 7,
and ran the exact same test: Access front end against ODBC linked tables. A
query that returns records that includes a bit field. Some records display 0
in the bit field, others display -1. I click in one of the records that
display -1, click Filter By Selection, and, presto: the query is filtered to
only show records with -1 in the bit field. No problem whatsoever.

Doing the exact same thing against the exact same table on the new server
running SQL 2005, when Filter By Selection is clicked while in a bit field
containing -1, no records are displayed. Somewhere along the way the -1 is
not translated to 1, and SQL finds no matches for -1 in the bit field.

I have no doubt you saw *something* related to bit fields; but it wasn't
this. I just did this a few minutes ago using an Access database against SQL
7 and SQL 2005, and I got different results. Only in SQL 2005 was there a
problem. No problem with filtering for -1 in the Access database when linked
to a SQL 7 database.

Neil

 

[Neil]

Good to know. Thanks.

Not tinyint but small integer. Like the Bit field, the tinyint is an
unsigned quantity on SQL-Server and cannot be used to store the value
of -1.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

That's a good point about bits not being portable. Using tinyint
certainly would have avoided this problem. I wonder what is the upside to
using bit instead of tinyint? Just being able to do boolean comparisons?

Neil

It's not a glitch, it's a fundamental problem with the translation
between VB (8086) Booleans and SQL Server (PDP) bit fields.

I recommend Never Use Bit Fields, because bit fields aren't really
portable. Use small integer fields instead of bit fields. This is not
just VB and SQL Server: bit fields aren't portable even in C.

Of course, bit fields are normally portable if you only use the zero
value: from "Writing Solid Code": "the portable range of a bit field is
0",
but bit fields are still a problem waiting to happen.

So you can write:

strSQL = strSQL & " AND [Field1]=" & Me.Field1CheckBox


strSQL = strSQL & " AND NOT ([Field1]<>" & Me.Field1CheckBox

but unless I was having trouble with database size I wouldn't do that.

By the way, dates have the same problem as Booleans: if you
use a numeric representation for a date, Access will translate for
SQL Server when it recognises a translation is required: if you
put in date 1 you won't have a record with date 1.

(david)

Not quite that simple. For example, I have a form in one place that has
a series of check boxes that represent bit fields. If the user wants to
return records for which that field is true, they check the box. The
code looks something like:

strSQL = strSQL & " AND [Field1]=" & Me.Field1CheckBox

Thus, it takes the value of the check box and uses it in the dynamic
SQL. The values are -1 and 0, which the ODBC driver sent to SQL Server
as True and False. That code would have to be entirely rewritten. (I've
created a wraparound function that converts the checkbox 0 and -1 to 0
and 1; but it'll have to be applied to the entire database, which is a
ton of code.)

The bottom line here, in my opinion, is that the ODBC driver continues
to DISPLAY -1 for True for data retrieved FROM SQL Server, but requires
us to SEND 1 for True for criteria sent TO SQL Server. That makes no
sense, and seems to be a glitch. I was hoping there was some sort of
patch or fix.

Neil


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message Instead of testing for 1 or -1, you should test for <> 0; this way,
you won't have to chase a moving target.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Have an Access MDB (2002/3 format) running against a SQL Server back
end, using ODBC linked tables. Recently upgraded from SQL 7 to SQL
2005.

With the bit fields in SQL Server, even though their values are 0 and
1 for False and True, the ODBC driver displays them as 0 and -1,
which are the Access False and True values. That was true for SQL 7
as well as for SQL 2005.

However, I find that when I run a query (not a pass-through; just a
regular Access query), I have to use "1" in the Where clause to get
True values from the SQL table, even though they are displayed as -1!

For example, "Select * From MyTable Where SomeBitField=-1" returns no
records; but "Select * From MyTable Where SomeBitField=1" returns
records. However, the records show -1 in the bit field!!!!

If I use True instead of 1 or -1 it works fine. But I have tons of
code that build dynamic SQL that use -1 for true, and I'd have to
change all of those.

Also, since the above is true for saved queries as well as dynamic
SQL, it seems to be some sort of glitch (since the saved query would
have to have "1" in the Criteria field, but would display "-1" in the
results!).

Does anyone know if this is a known problem and if Microsoft released
a patch or whatever for this? I'm running Access 2003, SP3 (v.
11.8166.8221).

Thanks!

Neil

 

[Neil]

That's interesting. Thanks.

Still, would the ODBC driver have been changed when we upgraded SQL Server?
If so, then that would explain it. If not, then it wouldn't. Windows/Access
weren't changed. Only the version of SQL Server. And SQL 2005 is problematic
in this regard, while SQL 7 wasn't.

Thanks.

Looking at the ODBC spec, I see that
"The SQL_BIT data type has different characteristics than the BIT type in
SQL-92."
It doesn't say what that means.

If this change was to align MS ODBC with the ODBC 3.x standard, it won't
be changed back - any fix will have to be in Access. (Actually, they
wouldn't
change anything in SQL Server for Access anyway, so I guess we knew that
already.)

Since, to my despair, they've never fixed any SQL Server/Access
compatibility
problems anywhere, I won't hold my breath for this either.

There is no ODBC Boolean type, and the ODBC bit type is unsigned char --
so if you are using ODBC, bits get translated to chars, then Booleans.

(david)

 

[Sylvain Lafontaine]

Could you run the SQL-Server Profiler and tell us what query exactly is sent
to either the SQL-Server 7 or 2000/5 when running this Filter By Selection
in Access?

Also, it will be interesting to see if your query where you are directly
testing against a Bit field using the value of -1 can run on this old
SQL-Server 7.

However and without any consideration to the above results, the fact that on
SQL-Server, the Bit field is an unsigned quantity that can only store the
values 0 and 1 should indicate you that you should code using only the
values 0 and 1 yourself when working against a Bit field. The fact that
Access will sometime show you the value of -1 instead of 1 when displaying
the value of a bit field can only be seen as a bug and you should never make
any code based on a bug.

Now that your code doesn't work anymore on SQL-Server 2005 is a strong
indication that MS has chosen to correct this bug on the SQL-Server side for
the version 2005 and later and I would be surprised if they ever go back.

 

[Rick Brandt]

And again, this behavior didn't start until we upgraded to SQL 2005.

I have no doubt you saw *something* related to bit fields; but it wasn't
this.

Sorry, but it was exactly what you are seeing. I could use "filter on
this value" or filter excluding this value" only on the zeros. If I did
so on the -1 values I got either all records or no records.

There are settings/options on SQL Servers regarding ANSI compliance and
behaviors. I would be more inclined to believe that your new server has
some of those settings different or that settings/options in the ODBC
driver and/or DSN are at work here and the fact that it is using SQL
Server 2005 is coincidental to the issue.

 

[lyle fairfield]

The only change re bits in SQL-Server 2005 of which I'm aware is that
of Cast and the strings 'True' and 'False'.

That is
Cast('False' as Bit) returns 0 and
Cast('True' as Bit) returns 1.

It's not readily apparent to me how this might have changed ODBC's
handling of bit fields.

But ODBC is an object of faith. When we use it, we have faith that it
will interpret our wishes to SQL-Server as we would like them
interpreted, and return from SQL-Server the results we expect. I'm
very impressed with ODBC's power, but not to the point of having such
faith, so I don't use ODBC.

I'm wondering if the OP could remedy the problem he faces with a
global search and replace on his check box code. If all the check
boxes have the same name this should be trivial. If not, it might
still be possible with a little ingenuity.

I note that no one has pointed out that directly referring to the
default (value) property of a checkbox
as in

Me.Checkbox1

rather than

Me.CheckBox1.Value

has a history of problems in Access/VBA. I doubt this is related to
the bit problem reported but I'd change that at the same time I was
remedying the other problem if I were doing this.

I think it's worthwhile to note that one of the best things for Access/
VBA is its implicit type conversion and its seamless use of default
properties. These seem great at the beginning. Access guesses what we
intend and it guesses right 99.44% of the time. But as we progress
these two "features" become "problems". A great number of the problems
we see here are directly attributable to these two characteristics of
Access/VBA, which have been called "EVIL" by outstanding VB coders.
For the last several years I have been, more and more, fully
qualifying all my object pointers, and doing explicit type conversion
with Cstr, CLng etc functions. I think this reduces errors and keeps
me, not some well-meaning C programmer in Redmond, in control of my
applications.

I may say that VBA's storage and handling of True and False are not
clearly documented and the descriptions here are not in keeping with
my experience which is that True is all bits on, with any bit on
coerced into all bits on. As a Byte, that is FF or 255. But Access by
default shows us Integers and Longs. When we turn all bits on in an
Integer or Long the sign bit is turned on and we are shown -1. Minus
one is 11 or 1111 or 11111111 and this may be helpful to remember, (It
is not 10000001). The notion of True = -1 is simply an incidental side
effect of the IEEE whatever number represtentation of signed integers.
In reality minus one has nada to do with True.

Sometime, (JET 4.0) Jet started storing true-false fields as bytes,
with a possible 8 such fields in one byte. TTBOMK this was
undocumented. This was drawn to my attention here (by poster TC I
believe) and I doubted it but a hex editor examination of a JET file
made me agree. I add this to point out that when we deal with Access/
JET or Access/SQL we are always talking about TWO technologies. They
may store and treat a data type in the same way, e.g., longs. Or they
may not, e.g., decimals and booleans.

 

[Neil]

New issue with this (with far more serious implications).

My forms have check boxes bound to the bit fields. These have been in place
for at least 8 years, and have never had a problem. Now Filter By Selection
against the check boxes does not work. Same issue, I assume: the -1
(checked) value is not being converted to 1 for SQL Server, and so no
matches are found.

There is no code here; and, again, this has been in place for many years.
Without any changes to the forms or to Access, this is no longer working.

I'm really puzzled that this hasn't come up with other people. Do those who
develop against SQL Server use versions prior to 2005? Do you not use bound
forms? Not use bit fields? Just seems very strange that this is so
problematic, and I seem to be the only one experiencing this.

Again, this is not a code issue. This is just: linking a SQL table using the
ODBC driver; binding a form to that linked table; and having check boxes
bound to the bit fields in the table. Filter By Selection does not work.
Prior to SQL 2005 it worked flawlessly.

Neil

 

[Neil]

I will run the profiler as you suggested. However, I want to say that I
strongly disagree with this statement:

The fact that Access will sometime show you the value of -1 instead of 1
when displaying the value of a bit field can only be seen as a bug

You're making the ASSUMPTION that it "sometimes" shows me a value of -1. I
never said that. This database has been in place for many years and it
ALWAYS shows a value of -1. To assume that it's a "sometime" thing (and
therefore a bug) is disingenuous, since that doesn't fit with the facts that
were presented.

Again, this is not a sometimes thing. For years and years and years Access
would present a bit field value of True as -1 in a query. It was not a bug
to be "fixed"!

Now that your code doesn't work anymore on SQL-Server 2005 is a strong
indication that MS has chosen to correct this bug on the SQL-Server side
for the version 2005 and later and I would be surprised if they ever go
back.

Well, if you read the post I just made (in response to the original post in
this thread), which starts with "New problem here," you'll see that this
issue didn't fix a bug; it created a bug.

Neil

 

[Neil]

Sorry, but it was exactly what you are seeing. I could use "filter on
this value" or filter excluding this value" only on the zeros. If I did
so on the -1 values I got either all records or no records.

There are settings/options on SQL Servers regarding ANSI compliance and
behaviors. I would be more inclined to believe that your new server has
some of those settings different or that settings/options in the ODBC
driver and/or DSN are at work here and the fact that it is using SQL
Server 2005 is coincidental to the issue.

I would say that that makes absolute, total, perfect sense. Now the question
is: where to look?

I don't think it's the DSN. The DSN to the new server was created in the
exact same way and with the exact same settings as the previous DSN. So I
don't think there's anything there.

And, unless the ODBC driver settings changed when SQL 2005 was installed,
that wouldn't be it. (I know that I didn't change any of them.)

So, any suggestions about where to look for the settings?

Thanks!

Neil

 

[Neil]

Sorry, but it was exactly what you are seeing. I could use "filter on
this value" or filter excluding this value" only on the zeros. If I did
so on the -1 values I got either all records or no records.

There are settings/options on SQL Servers regarding ANSI compliance and
behaviors. I would be more inclined to believe that your new server has
some of those settings different or that settings/options in the ODBC
driver and/or DSN are at work here and the fact that it is using SQL
Server 2005 is coincidental to the issue.

OK, I toggled all the options under the database options, miscellaneous
section (including the ANSI options). No change. I didn't do Refresh Links
afterwards, as that didn't seem necessary.

 

[Neil]

Sorry, but it was exactly what you are seeing. I could use "filter on
this value" or filter excluding this value" only on the zeros. If I did
so on the -1 values I got either all records or no records.

There are settings/options on SQL Servers regarding ANSI compliance and
behaviors. I would be more inclined to believe that your new server has
some of those settings different or that settings/options in the ODBC
driver and/or DSN are at work here and the fact that it is using SQL
Server 2005 is coincidental to the issue.

Well, it was a good theory; but it wasn't the ANSI compliance settings or
the ODBC driver settings or the DSN. It is the version of SQL Server and how
it interprets commands.

I changed the SQL database compatibility level back from SQL 2005 (90) to
SQL 7 (70) and the problem went away. Changed it back to 90, the problem
returned.

So that proves that the issue is with SQL and how it works with data, not
with anything else.

I also changed it to 80 (SQL 2000) and the problem was there, too.

So, when you encountered this problem in the past, you must have been using
a version of SQL Server that was at least 2000, correct? I guess the reason
I haven't encountered this problem is because I've been using SQL 7 all
these years.

(And if this problem existed in SQL 2000 and wasn't fixed in Access 2002/3
and their SPs, I'm pretty sure it's not going to go away. )

<>

So the issue is: what to do about it. I can't just change my code, which
initially seemed like a good approach. This affects the built-in Filter By
Selection in forms. And the users are too used to that functionality for me
to tell them they can't use it.

So, the options I see are:

1) Keep the SQL compatibility level at 70. (Not a good idea. Can never move
on from there. And once MS stops supporting that, would be SOL.)

2) Provide the users with a custom Filter By Selection function. A workable
idea; but would be much better to use the built-in functions.

3) Change all my bit fields to smallint (as David suggested). That seems to
be the best solution, unless it would introduce other problems/glitches?

4) Other?

Thanks so much for the help!!

Neil

 

[Rick Brandt]

Well, it was a good theory; but it wasn't the ANSI compliance settings
or the ODBC driver settings or the DSN. It is the version of SQL Server
and how it interprets commands.

I changed the SQL database compatibility level back from SQL 2005 (90)
to SQL 7 (70) and the problem went away. Changed it back to 90, the
problem returned.

So that proves that the issue is with SQL and how it works with data,
not with anything else.

I also changed it to 80 (SQL 2000) and the problem was there, too.

So, when you encountered this problem in the past, you must have been
using a version of SQL Server that was at least 2000, correct? I guess
the reason I haven't encountered this problem is because I've been using
SQL 7 all these years.

I can't dispute what you are seeing, but I saw this behavior positively
when using both SS 6.5 and SS 7. I don't know about 2000 because by the
time we moved to that I had already gotten into the habit of always
testing for =0 or <>0 and not using bit fields any longer.