Problem With Bit Fields When Running Against SQL 2005

[Neil]

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

 

[Sylvain Lafontaine]

Instead of testing for 1 or -1, you should test for <> 0; this way, you
won't have to chase a moving target.

 

[Neil]

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

 

[Albert D. Kallal]

Hum...the problem here is that while it displays as -1, it actually a
boolean value.

You can open up the linked table in design mode and change the formatting to

yes/no
on/off
true/false

And, then you can save your table. Note that you can sue

MyBooleanfield = on
MyBooleanfield = True
MyBooleanfield = Yes

And the sql will work regardless of your formatting settings for that
linked table...

Also, note in the access debug window:

? true and -1
-1
? true and cbool(-1)
True

So, that -1 is only for display in that linked table, but it is STILL a
true/false value. So, you don't want to use the "number" -1, as that is not
the same thing as true/false.

And, worse, if the odbc driver was changed to fix your problem, it would
likely break others peoples applications.

If you not testing for -1, but REALLY want a true/false value, then I would
simply do a search/replace in all of your sql and use "yes", or "true".

Perhaps someone else jumps in here with a fix of some type.

At the end of the day "jets" idea of true/false is somewhat different then
that of sql server, and the only way around this trouble is to use
true/false in your sql, not a number like -1 that happens to luck out and
evaluate to true when using local tables...

I never had this problem, but then again I never used -1 for my sql, and
never even thought of doing so.

I do however agree that -1/0 should be a legal replacement for the follwing
sets:

true/false
yes/no
on/off

Note that all of the above combos work for linked tables (true,yes,on). In
fact the 3 "true" values work on non linked tables also. So, it is just
that -1/0 is not really considered the same thing when using linked
tables...

 

[Gina Whipp]

I USED to use -1/0 until I started working with SQL Servers, then quickly
learned much easier to use True/False. Especially when you consider any one
of my Clients that does not have an SQL Server back-end might decide to move
their data to one and I don't want to have to hunt through all that code to
find WHERE I MIGHT have used -1/0.

 

[Albert D. Kallal]

I USED to use -1/0 until I started working with SQL Servers, then quickly
learned much easier to use True/False. Especially when you consider any
one of my Clients that does not have an SQL Server back-end might decide to
move their data to one and I don't want to have to hunt through all that
code to find WHERE I MIGHT have used -1/0.

I am lucky...I never used -1.

Years ago we were writing code in a language that did NOT have a pre-defined
true/false constant, but certainly the if...then structured respected the
concept of 0 = false, and any positive number = true.

So, we used to write in our setup code:

const True = 1
const False = 0

However, one of our developers having written code on MANY platforms used:

const True = (1 = 1)
const False = (1 = 0)

The above is a far better approach and thus means one does not care what the
internal number is used for "true". (we really never should have to anyway).

This issue also has ramifications for the ever increasing amount of data
that is in xml format which in fact is just text data and not bitwise data
in an internal database format that may dependson bitwise comparisons...

 

[Neil]

Thanks, Albert. But note that, per the example I gave in my second post
here, sometimes I would take the value of an unbound check box, and use that
as a value to check against in my dynamic SQL. Here there's no hard-coding
of "-1" or "1"; just saying: "return records where this bit field is equal
to the state of this check box." And Access returns -1 or 0 as values for
the unbound check box, not True or False.

So I'm just saying it should be consistent. The ODBC driver in the past took
True from SQL and returned it as -1 in Access; and took -1 in Access and
sent it as True to SQL. It should be able to continue to do that.

And I understand what you're saying about -1 not really being true, and can
format the table to display yes/no or whatever. But when you don't format
the table and just look at the raw values that the ODBC driver is returning,
it returns -1 for True; but it now requires to SEND 1, not -1, for True. I'm
just saying it should be consistent with how it interprets SQL data.

The problem's not the ODBC driver's fault, per se, since Microsoft
apparently made changes to SQL Server in version 2005. That's why I figured
that a newer version of the ODBC driver would resolve that. But apparently
not.

Thanks,

Neil

 

[Gina Whipp]

"Years ago we were writing code ..." Hmmmm, I must be ALOT younger then!


I only began writing code with Access 2.0, still kinda miss it. Made you
THINK outside of the box a lot more. Only in the past 5 years have I had
Clients start migrating and made me rethink the -1/0 scenario. And now I
just write as if they are going to migrate...

 

[Neil]

I started with Access 2.0 as well. But I don't miss it. Was glad to see it
go! I liked Access 97. But I have to admit I like the newer versions, 2003,
etc., the best. Haven't had a desire to move to 2007 yet.

About the sit with -1/0, I agree that hard-coding it isn't a good idea in
case one has to migrate. But, still, one would expect consistency that data
representing True would be the same in both directions!

And, as I noted in a subsequent post to Albert, the problem arises also when
you use the state of an unbound check box as a True/False value. I mean, you
should be able to do that, right? But Access returns -1 as the state of a
checked box, and that won't fly if you pass that directly to SQL Server now.

So, apart from the whole -1/0 issue, it seems to me that if you can't even
rely on the database to give you True/False values from a check box, then
there's a problem! I should be able to say "Return records where this bit
field = the state of this unbound check box." But I can't. The check box
is -1, and the SQL now requires 1. So that's different than the -1/0 issue.

Thanks!

 

[Stefan Hoffmann]

hi Neil,

With the bit fields in SQL Server, even though their values are 0 and 1 for
False and True

This is not correct. A bit field is either set (1) or not set (0). These
values are not equal to true/false. SQL Server has no boolean field data
type, but a BOOL data type to hold the result of logic operations.

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!

In VBA False is defined as 0 and True as Not False. This is as a signed

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

Both queries should return the same values. But there was an issue with
Access/Jet and the BIT data type.

http://support.microsoft.com/default.aspx?scid=kb;en-us;943967&sd=rss&spid=2509

mfG
--> stefan <--

 

[Sylvain Lafontaine]

Unlike with JET/Access, there is no such thing as True and False values or
fields in SQL-Server; so the ODBC driver cannot send a True or False value
to SQL-Server because these two constants don't exist in T-SQL and the Bit
field on SQL-Server can only be used to store the values 0 or 1, not the
values True or False.

Depending on how the table is linked and the properties set up, the values
of a Bit field can be shown as 0 and -1 in Access instead of 0 and 1;
however, this is an Access thing and SQL-Server has nothing to do with it
and for SQL-Server, -1 and 1 are two differents values; whatever Access
might think or display and there is no patch or fix that will *magically*
convert or transform the value of 1 as -1 on SQL-Server when this value is
stored in any numerical field; even when a Bit field is used

If you want to store the values of True and False as -1 and 0 on SQL-Server,
you need to use the small integer (smallint, two bytes long but other,
bigger types such as Int or BIGINT can be used, too) data type instead of a
Bit field if you don't want to change your code.

If you don't want to replace the Bit fields with SmallInt fields, the other
solution is to change your code in order to test for <> 0; this way, your
code will be compatible with both Access and T-SQL.

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

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

Instead of testing for 1 or -1, you should test for <> 0; this way, you
won't have to chase a moving target.

 

[Albert D. Kallal]

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

The problem is that for MOST form values, you have to pre-process the values
anyway.

eg:

strSQL = strSQL & " AND City = " & Me.city

Note how the above will not work, you need:

strSQL = strSQL & " AND City = '" & Me.city & "'"

Same goes for date:

strSQL = strSQL & " AND InvoiceDate = " & Me.InvoiceDate

You need:

strSQL = strSQL & " AND InvoiceDate = #" &
format(Me.InvoiceDate,"mm/dd/yyyy") & "#"

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.

It might not actually be the odbc driver that is doing that. In fact, try
the follwing:

Dim r As dao.Recordset
Set r = CurrentDb.OpenRecordset("dbo_contacts")

Do While r.EOF = False
Debug.Print r!MyTrueFals
r.MoveNext
Loop
r.Close

The above returns True, or false...not -1/0. (and, this is a linked table to
sql server).

As I said, you can go into the linked table, and modify the column to return
on/off yes/no, or true/false. So, you CAN change what it displays here.

Access does often display "-1" for true, or 0 for false. That is likely
jet/access or vba that is doing the translation for you. However, the odbc
driver cannot be changed because other products beside ms-access use that
driver. The root problem here is that sql server uses 0 and 1 while jet
uses -1, and 0 for true false (likely due to be compatible with vba).

Jet was built/designed many years before Microsoft purchased Sybase which
then became sql server.

As my above examples showed, you DO NEED some pre-processing here anyway
for "most" cases when you do a sql string and a condition from a form...

So, you have really two choices:

1) Build a function that converts the -1, 0 to True/false and use that (then
let jet translator convert this for you to Boolean). Doing this means
you would not need to change code when switching from jet to sql server.

2) Build a function that converts the -1 to 1, and have some setting for
when
using jet based applications as opposed to sql server.

I prefer solution #1. Note that you have the same problem with the odbc
driver to Oracle, MySql etc...

In fact, because I hate having to put quotes around so many strings in my
code, especially code for sql stuff, then for all of my applications, I had
two functions:

qu()
qudate()

And, now, as you pointed out, I would need something called
quBool()

Thus, I would have written the 3 above sql examples as:

strSQL = strSQL & " AND City = " & qu(Me.city)

strSQL = strSQL & " AND InvoiceDate = " & qudate(Me.InvoiceDate)

strSQL = strSQL & " AND [Field1] = " & qubool(Me.Field1CheckBox)

So, note that for 2 out the above 3 examples, we PREVIOUSLY had to
pre-process
the value in the control on the form anyway. So it never like you could just
use a form value in the string anyway, the exception being your luck with
the true/false example you have...

I think for quBool, I would have it return True/False.

I DO agree that Jet should accept -1/0 in addition to yes/no, on/off,
true/false as it does now. So, there is some translation going on for those
3 allowable values. I suppose jet should do so for -1/0 also, but it does
not...

The qu, and quDate functions are posted after my sig.



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)

Function qu(vText As Variant) As String
' takes a string and surrounds it with double quotes
' All " (double quotes) are converted to ' (single quotes) before
' this is done to prevent errors in the sql string

If IsNull(vText) = False Then
If InStr(vText, Chr(34)) > 0 Then
vText = strDReplace(CStr(vText), Chr(34), "'")
End If
End If

qu = Chr$(34) & vText & Chr$(34)

End Function

Public Function qudate(mydate As Variant) As String

' returns a formatted string of date, surrounded with # signs
' format is mm/dd/yyyy as required for sql, regardless of date setting

If IsNull(mydate) = True Then
qudate = ""
Else
qudate = "#" & Format(mydate, "mm/dd/yyyy") & "#"
End If

End Function

Public Function quS(mystring As String) As String

' returns a string surround by quotes WITH
' A asterisk ie: test becomes "test*"
' this is used for wild card searches

quS = Chr$(34) & mystring & "*" & Chr$(34)

End Function

Public Function quBool(cYesNo As Boolean) As String

' returns a True or False string based on a bol value

If cYesNo = True Then
quBool = "True"
Else
quBool = "False"
End If

End Function

 

[david]

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

Instead of testing for 1 or -1, you should test for <> 0; this way, you
won't have to chase a moving target.

 

[Paul Shapiro]

At one time there was an issue with Access using a SQL Server bit field that
allowed nulls. If nulls are allowed, you could try setting No Null instead.

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

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

 

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

or, you could use 'Access Data Projects' and you'd never have to
translate anything anywhere, because everything is native SQL land.

only when you start mixing Jet and SQL Server does this begin to be a
pain!!!

1 = true
0 = false

At one time there was an issue with Access using a SQL Server bit field that
allowed nulls. If nulls are allowed, you could try setting No Null instead.

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 continuesto
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 messageInstead 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" returnsno
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 haveto
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).- Hide quoted text -

- Show quoted text -

 

[Neil]

Sylvain:

I think you missed my point entirely. I'm not looking for a "magical fix"
for my code. I'm looking for consistency in the ODBC driver -- which brings
in a bit value of 1 as -1 from SQL Server, but requires it to be sent to SQL
Server as 1 to match records. Consistency, not magic.

Whether I used the term "true" or "1,", I certainly understand that SQL
Server stores bit values as 1. Access however, sees True as -1. That is not
a formatting issue. ? cint(True) in Access returns -1. ? cint(1=1)
returns -1. ? cint(Me!SomeCheckedCheckbox) returns -1. -1 is Access' boolean
True value.

The role of the ODBC driver is to translate between the two platforms. Up
until SQL 2005, that worked fine. With changes in 2005, it doesn't.

So if I did:

"Select * from MyTable Where MyBitField=" & Me!MyCheckBox

you would expect the ODBC driver to be able to handle that. But since Access
returns -1 for the state of the checkbox, this code fails with SQL 2005. In
previous versions of SQL Server, the code worked fine.

Bit fields have always stored 0 and 1 in SQL Server. So, in previous
versions of SQL Server, when Access returned -1 for Me!MyCheckBox, the
driver was able to translate it correctly. With SQL 2005 it doesn't.

Access' use of -1 for True hasn't changed. SQL Server's use of 1 for True
hasn't changed. But something changed so that the above code, which used to
work, doesn't work.

Again, if the ODBC driver is going to PULL values as "-1" from a bit field,
then it should send them as "-1" to the bit field. But running "Select *
From MyTable Where SomeBitField=1" against SQL Server and having the records
that match the criteria of SomeBitField=1 actually contain -1 in that field
when displayed in Access doesn't make any sense.

And, again, -1 is not a formatting issue. It's the underlying value of the
field when no formatting is applied. Access presents the value retrieved
from SQL as -1, but now requires the actual SQL value of 1 as criteria to
get those records. That makes no sense at all.

Unlike with JET/Access, there is no such thing as True and False values or
fields in SQL-Server; so the ODBC driver cannot send a True or False value
to SQL-Server because these two constants don't exist in T-SQL and the Bit
field on SQL-Server can only be used to store the values 0 or 1, not the
values True or False.

Depending on how the table is linked and the properties set up, the values
of a Bit field can be shown as 0 and -1 in Access instead of 0 and 1;
however, this is an Access thing and SQL-Server has nothing to do with it
and for SQL-Server, -1 and 1 are two differents values; whatever Access
might think or display and there is no patch or fix that will *magically*
convert or transform the value of 1 as -1 on SQL-Server when this value is
stored in any numerical field; even when a Bit field is used

If you want to store the values of True and False as -1 and 0 on
SQL-Server, you need to use the small integer (smallint, two bytes long
but other, bigger types such as Int or BIGINT can be used, too) data type
instead of a Bit field if you don't want to change your code.

If you don't want to replace the Bit fields with SmallInt fields, the
other solution is to change your code in order to test for <> 0; this way,
your code will be compatible with both Access and T-SQL.

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

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

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]

Sylvain:

I think you missed my point entirely. I'm not looking for a "magical
fix" for my code. I'm looking for consistency in the ODBC driver --
which brings in a bit value of 1 as -1 from SQL Server, but requires it
to be sent to SQL Server as 1 to match records. Consistency, not magic.

Whether I used the term "true" or "1,", I certainly understand that SQL
Server stores bit values as 1. Access however, sees True as -1. That is
not a formatting issue. ? cint(True) in Access returns -1. ? cint(1=1)
returns -1. ? cint(Me!SomeCheckedCheckbox) returns -1. -1 is Access'
boolean True value.

The role of the ODBC driver is to translate between the two platforms.
Up until SQL 2005, that worked fine. With changes in 2005, it doesn't.

So if I did:

"Select * from MyTable Where MyBitField=" & Me!MyCheckBox

you would expect the ODBC driver to be able to handle that. But since
Access returns -1 for the state of the checkbox, this code fails with
SQL 2005. In previous versions of SQL Server, the code worked fine.

Bit fields have always stored 0 and 1 in SQL Server. So, in previous
versions of SQL Server, when Access returned -1 for Me!MyCheckBox, the
driver was able to translate it correctly. With SQL 2005 it doesn't.

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.

I have always tested for =0 or <>0 and gave up using but field entirely
years ago.

 

[Neil]

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

The problem is that for MOST form values, you have to pre-process the
values
anyway.

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

It's not like as though I'm saying it *should* do this; I'm saying it *did*
do this -- used -1 as True in Access and converted it to 1 when sent to SQL.

It might not actually be the odbc driver that is doing that. In fact, try
the follwing:

Dim r As dao.Recordset
Set r = CurrentDb.OpenRecordset("dbo_contacts")

Do While r.EOF = False
Debug.Print r!MyTrueFals
r.MoveNext
Loop
r.Close

The above returns True, or false...not -1/0. (and, this is a linked table
to
sql server).

But "True" is just a built-in constant in Access. cint(True) returns -1. I
thought that was obvious. There is "True" entity. It's just a built-in
constant that contains a value (in this case -1).

Simply because Access shows "True" in the debug window doesn't mean that
that "True" doesn't translate to -1.

As I said, you can go into the linked table, and modify the column to
return
on/off yes/no, or true/false. So, you CAN change what it displays here.

Again, not talking about formatting. Talking about underlying values. Don't
care what's "displayed." Only what the underlying value is. Access sees all
of those as -1.

Access does often display "-1" for true, or 0 for false. That is likely
jet/access or vba that is doing the translation for you. However, the odbc
driver cannot be changed because other products beside ms-access use that
driver. The root problem here is that sql server uses 0 and 1 while jet
uses -1, and 0 for true false (likely due to be compatible with vba).

Well, whether it's the ODBC driver or Jet, I don't know. I just assumed it
was the ODBC driver.

But whatever it is, the point is that, yes, it did the translating for me,
so that, while Access used -1 for True, and SQL used 1 for True, the two
values would be translated into each other. If I did:

"Select * From MyTable Where SomeBitField=-1"

records with -1 in SomeBitField would be returned.

Now, with 2005, one needs to do:

"Select * From MyTable Where SomeBitField=1"

but the records still display -1!

So I'm saying "Give me records where this field is 1"; but I'm getting
records that disaplay -1! Not consistent. And nothing's changed on the
Access/Jet end. Only on the SQL end. So any translating that it had done,
one would think it would continue to do. SQL's always seen True as 1, and
Access always as -1.

Jet was built/designed many years before Microsoft purchased Sybase which
then became sql server.

As my above examples showed, you DO NEED some pre-processing here anyway
for "most" cases when you do a sql string and a condition from a form...

So, you have really two choices:

1) Build a function that converts the -1, 0 to True/false and use that
(then
let jet translator convert this for you to Boolean). Doing this means
you would not need to change code when switching from jet to sql server.

I guess there's the rub. Even though True has a value of -1, Jet knows to
translate it to 1. And, when I used -1 instead of True with versions of SQL
Server prior to 2005, Jet also knew to translate that to 1. But, for some
reason, when working with SQL Server 2005, it now no longer translates -1 to
1, though it still will translate True to 1 (even though True is -1).

The irony here is that when I started coding years ago, I used -1 instead of
True because I thought it was more accurate, that "True" was just a
shorthand notation for the "actual" value of -1. Ironic.

2) Build a function that converts the -1 to 1, and have some setting for
when
using jet based applications as opposed to sql server.

I prefer solution #1. Note that you have the same problem with the odbc
driver to Oracle, MySql etc...

In fact, because I hate having to put quotes around so many strings in my
code, especially code for sql stuff, then for all of my applications, I
had
two functions:

qu()
qudate()

And, now, as you pointed out, I would need something called
quBool()

Thus, I would have written the 3 above sql examples as:

strSQL = strSQL & " AND City = " & qu(Me.city)

strSQL = strSQL & " AND InvoiceDate = " & qudate(Me.InvoiceDate)

strSQL = strSQL & " AND [Field1] = " & qubool(Me.Field1CheckBox)

So, note that for 2 out the above 3 examples, we PREVIOUSLY had to
pre-process
the value in the control on the form anyway. So it never like you could
just
use a form value in the string anyway, the exception being your luck with
the true/false example you have...

I think for quBool, I would have it return True/False.

I DO agree that Jet should accept -1/0 in addition to yes/no, on/off,
true/false as it does now. So, there is some translation going on for
those
3 allowable values. I suppose jet should do so for -1/0 also, but it does
not...

But it used to, and still does, when working with versions of SQL Server
prior to 2005. That's my point.

The qu, and quDate functions are posted after my sig.

Thanks! Appreciate your assistance.

Neil

 

[Neil]

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

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

 

[lyle fairfield]

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

While this is not directly relevant to your question, perhaps a few
others could benefit in being reminded that there is an alternative to
ODBC which will not have this problem when implemented properly.

ADPs and ADO use T-SQL directly, (obviously not but to the user it
seems that it's directly.)

My experience is that ADO handles bit fields flawlessly.

When I want something to be sound I use the ADO parameter object. This
lets us specify the type of parameter; I have never known this method
to fail.