Yes/no field transformed to 0/-1

[hansnyc]

I hava a field on a tbl set to yes/no on my form it is a check box
m dba is linked to a sql server.
recently i modified this table by extending the char size of a txt field via sql manager since you cant modify linked table within access.
i erased the linked table in my dba, relinked it and since then my field yes/no is in 0 or -1. I could care less since it is the way SQL stores it anyway BUT
I have a listbox on my switchboard showing me data from this tbl. And now it says 0 or -1 insead of yes or no. It is confusing my users
how can i modify this?
i can't format an item in the list box query.
Please a solution!
thanks

 

[Banana]

See if conditional formatting can do the trick for you. Search the help or the board to learn more about conditional formatting.

 

[Rich]

i can't format an item in the list box query.

Why not ?

 

[xmann]

I'm not an expert in anyway, but I've tried the checkbox before. What I did:-

In SQL, the field's data type is bit, length of course is 1
In Access, for the checkbox, the field's data type is Yes/No

Also, someone may also encounter this, you may not be able to modify property in linked tables but somehow after tweaking with certain 'attributes' for a particular field, the changes were accepted. Sorry to confuse you but I seem to remember 'forcing' Access to accept a setting this way

Anyways my post may not help you to solve it immediately but hopefully it rings a bell or give you a light bulb

 

[Ian Mac]

FIrstly Accesses Yes/No or True/False etc is of the Type 0/-1 and has a field size of 1, see Data Type from the help menu.

You can change the values in the query, where you might have

SELECT ThatField FROM....

You can change it to

SELECT IIF(ThatField,"Yes","No") As YesNo FROM....

That confusion arises when Access is being to helpful by allowing you to have Yes/No and putting in nice Checkboxes for you. In actual fact it is really 0 False/-1 True.

 

[hansnyc]

do i type in the criteria section of the query under that field
If(thatfield, "yes", "no") as yesno ?
that doesn't seem to work
any other code I could use?

 

[Ian Mac]

Did you notice there are 2 I's in IIF?

It's an immediate if (IIF) not just IF.