Bit fields with Access97 and SQL2000

[Eugenio]

Hi

Sorry in advance for my english. I hope you'll understand me.

I have an application devoloped by MSAccess97 with MSSQLserver 7.0.
Now, I have to change DB from MSSQLserver 7.0 to MSSQL2000,
but unfortunately I have some problems with bit fields, because the new OBDC
considers 1 the value bit fields instead -1 as the old ODBC of SQL7.
So there are a lot query in combobox, query and code to compare the value
as -1
and I can't exctract any data.

Is it possible? Anyone's had the same problem?

In Microsoft web site I didn't forund anything about this problems.
Can you send me any link about this problem?

Thanks
Bye
Eugenio

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The Bit data type can only hold 1, 0 or NULL. If you want to have -1 as
a value you must convert the data type from Bit to Smallint. If you do
that on the current data, afterwards you will have to change all 1s to
1s - use an UPDATE statement.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlG0y4echKqOuFEgEQKPogCbBwy5lN/NJ60sf4ZdwgGuSlzPfkEAoIGd
QWXNVneDtMLS/w3ljcTharHm
=3tSx
-----END PGP SIGNATURE-----
Eugenio wrote:

Hi

Sorry in advance for my english. I hope you'll understand me.

I have an application devoloped by MSAccess97 with MSSQLserver 7.0.
Now, I have to change DB from MSSQLserver 7.0 to MSSQL2000,
but unfortunately I have some problems with bit fields, because the new OBDC
considers 1 the value bit fields instead -1 as the old ODBC of SQL7.
So there are a lot query in combobox, query and code to compare the value
as -1
and I can't exctract any data.

Is it possible? Anyone's had the same problem?

In Microsoft web site I didn't forund anything about this problems.
Can you send me any link about this problem?

[Steve Jorgensen]

On Mon, 04 Apr 2005 21:42:23 GMT, MGFoster <[e-mail]> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The Bit data type can only hold 1, 0 or NULL. If you want to have -1 as
a value you must convert the data type from Bit to Smallint. If you do
that on the current data, afterwards you will have to change all 1s to
1s - use an UPDATE statement.

Actually, Jet usually maps bit fields to -1/0 so they'lll work correctly with
check box controls and such. I'm not sure why that is not happening in this
case.

[MGFoster]

Steve Jorgensen wrote:

On Mon, 04 Apr 2005 21:42:23 GMT, MGFoster <[e-mail]> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The Bit data type can only hold 1, 0 or NULL. If you want to have -1 as
a value you must convert the data type from Bit to Smallint. If you do
that on the current data, afterwards you will have to change all 1s to
1s - use an UPDATE statement.

Actually, Jet usually maps bit fields to -1/0 so they'lll work correctly with
check box controls and such. I'm not sure why that is not happening in this
case.

Yeah, but it wasn't JET he converted from; he converted from SQL Server
7.0 to SQL Server 2000. According to BOL that's where the conversion of
negative 1 to positive 1 took place.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Rick Brandt]

"MGFoster" <[e-mail]> wrote in message
news:Id***************@newsread3.news.pas.earthlin k.net...

Steve Jorgensen wrote:

On Mon, 04 Apr 2005 21:42:23 GMT, MGFoster <[e-mail]> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The Bit data type can only hold 1, 0 or NULL. If you want to have -1 as
a value you must convert the data type from Bit to Smallint. If you do
that on the current data, afterwards you will have to change all 1s to
1s - use an UPDATE statement.

Actually, Jet usually maps bit fields to -1/0 so they'lll work correctly with
check box controls and such. I'm not sure why that is not happening in this
case.

Yeah, but it wasn't JET he converted from; he converted from SQL Server 7.0 to
SQL Server 2000. According to BOL that's where the conversion of negative 1
to positive 1 took place.

We first started using SQL Server at version 6.5 and it was already using
positive one for bit fields. Perhaps the change that is really causing the
problem is the change that made bit fields nullable. That definitely was
changed after 6.5.

I found in 6.5 that even though Access presented True bit fields as a negative
one that tests against the field for "True" or -1 or 1 were all unreliable.
Ever since I have always tested for =0 or <>0.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[David W. Fenton]

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:AX****************@newssvr11.news.prodigy.com :

I found in 6.5 that even though Access presented True bit fields
as a negative one that tests against the field for "True" or -1 or
1 were all unreliable. Ever since I have always tested for =0 or
<>0.

Actually, that is the way True/False are implemented in Jet and VBA.
True actually *does* mean <>0. Take this example:

IIf(InStr("12345","5"),"True","False")

It returns the string "True," even though if you test it against the
value of True, it returns False.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Rick Brandt]

David W. Fenton wrote:

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:AX****************@newssvr11.news.prodigy.com :

I found in 6.5 that even though Access presented True bit fields
as a negative one that tests against the field for "True" or -1 or
1 were all unreliable. Ever since I have always tested for =0 or
<>0.

Actually, that is the way True/False are implemented in Jet and VBA.
True actually *does* mean <>0. Take this example:

IIf(InStr("12345","5"),"True","False")

It returns the string "True," even though if you test it against the
value of True, it returns False.

Yes I was aware of that. I just found that against Jet tables I could
use...

SELECT * FROM TableName
WHERE YesNoField = -1

....and it was reliable whereas it was not reliable against a SQL Server
linked table. In fact if I clicked in a SQL Server linked field that was
displaying -1 and used the filter tool "Filter by Selection" it would not
work. I assume because it was passing "WHERE foo = -1" to the server and
the server was not finding any such records even though that is how Access
was displaying them.

*Sometimes* I could filter for =1 and even though I could clearly "see" no
records with that value it would return all the -1 values. That behavior
was not consistent though. =0 and <>0 were the only criteria I found that
worked 100 percent of the time.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com