Incorrect Interpretation of BIT field on Linked Table

  • Thread starter Thread starter Kurt
  • Start date Start date
K

Kurt

Using Access 97:
When linked to an external table that contains a field of
type BIT, the true values show as -1, instead of 1, since
Access interpretes the field type to be a Yes/No field,
which is -1 and 0 respectively. The -1 is confusing my
users and I am interested to know if anyone has come up
with a way around this or a fix of some kind...

Any help would be greatly appreciated!
Kurt
 
Kurt said:
Using Access 97:
When linked to an external table that contains a field of
type BIT, the true values show as -1, instead of 1, since
Access interpretes the field type to be a Yes/No field,
which is -1 and 0 respectively. The -1 is confusing my
users and I am interested to know if anyone has come up
with a way around this or a fix of some kind...

Any help would be greatly appreciated!
Kurt
Click to expand...

I wouldn't describe that as an "incorrect" interpretation of the field,
exactly, since the Jet BIT data type represents True as -1 and False as
0. It's trying to do its best to translate the data to the correct
internal format. Still, I can see how users looking at the values in
tables might be surprised, if they're used to viewing BIT data as 1 or
0.

I'm assuming that you can't just keep your users out of datasheet views,
or this wouldn't be a problem. Can you substitute queries on the linked
tables for the tables themselves, and get by with using the
"True/False", "Yes/No" or "Off/On" formats on the fields in the queries?
I'm afraid those will still show the -1 value if the user clicks in the
cell to edit it, though. Or you could set the "Display Control" for the
query fields to "Check Box"?

If you don't need the field to be updatable, of course, you could use a
calculated field with an expression like Abs([FieldName]) in place of
[FieldName] itself.

Aside from things like these, the only things I can think of are (a)
change the fields in source tables from bit to smallint, or (b) educate
your users. But I could easily have overlooked something.
 
Thank you Dirk, I appreciate the help!
-----Original Message-----
Using Access 97:
When linked to an external table that contains a field of
type BIT, the true values show as -1, instead of 1, since
Access interpretes the field type to be a Yes/No field,
which is -1 and 0 respectively. The -1 is confusing my
users and I am interested to know if anyone has come up
with a way around this or a fix of some kind...

Any help would be greatly appreciated!
Kurt
Click to expand...

I wouldn't describe that as an "incorrect" interpretation of the field,
exactly, since the Jet BIT data type represents True as - 1 and False as
0. It's trying to do its best to translate the data to the correct
internal format. Still, I can see how users looking at the values in
tables might be surprised, if they're used to viewing BIT data as 1 or
0.

I'm assuming that you can't just keep your users out of datasheet views,
or this wouldn't be a problem. Can you substitute queries on the linked
tables for the tables themselves, and get by with using the
"True/False", "Yes/No" or "Off/On" formats on the fields in the queries?
I'm afraid those will still show the -1 value if the user clicks in the
cell to edit it, though. Or you could set the "Display Control" for the
query fields to "Check Box"?

If you don't need the field to be updatable, of course, you could use a
calculated field with an expression like Abs ([FieldName]) in place of
[FieldName] itself.

Aside from things like these, the only things I can think of are (a)
change the fields in source tables from bit to smallint, or (b) educate
your users. But I could easily have overlooked something.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Back
Top