converting yes/no for sql server

[tagteam]

I am preparing to move my data to SQL server for the backend so i created a new field in my table for each yes/no field and set it to bye so I could use 0 for no and 1 for yes (and 0 for false and 1 for true) however, the checkbox field wants to use 0 and -1. I will still use access to display my data and checkboxes are very easy for the end user to understand.



What is the best practice for this?
1.set the field to integer so that I can still use 0 and -1 for fields and the checkbox will work fine. Then use smallint in the sql server
2.set the field to byte and use 0 and 1. Then in every form or query have to use a case or iff statement to make 1 show as yes and 0 as no. Then use bit or tiny int in the sql server
3.some other option.

Any help is appreciated. I am going to have to do this for several fields so I would like to get it figured out and correct the first time so I dont have to go back and change anything.

I have also read that Bit in SQL server will be 0 and 1 but will display in access as 0 and -1 and that should make the checkboxes work. Does anyone have any confirmation on this?

[June7]

I have Access linking to SQLServer Express. When I view table in Design it shows Yes/No field type with 0 and -1 values. In SSMS it shows as Bit type. Yes, checkbox works.

[Gicu]

Bit data type is the right one for SQL Server, but make sure you set Allow Nulls to False and set a default value (either 0 or 1 depending on your needs). Allowing Nulls with Bit fields will create problems, lots of info on that on the web.
Cheers,

[tagteam]

Thank, I tried it today an loaded it up in a test database andit did indeed show as 1 in SQL server and -1 in access. However, when i tried to use a filter on my form and filter for checked it did not work. I can see that it shows -1 as a value in the table but the filter by form doesnt work for it. I am going to redo it now, make sure that field is set to not allow nulls and try it again.

[June7]

I don't use FilterByForm. Just tested and it doesn't really work for any field from SQLServer table. Only options are Is Null and Is Not Null. So checkbox fails here. A textbox bound to this field will also have Is Null and Is Not Null options.

[tagteam]

Yeah, I updated the table to no allow nulls for those fields and they still didnt work with the check box. My users use the checkbox search feature a lot so I will have to figure out a work around.

[June7]

Either use right click filter or build your own filter form. Review http://allenbrowne.com/ser-62.html

Might also find another Allen Browne article interesting http://allenbrowne.com/NoYesNo.html

[tagteam]

Thanks june7 I have read the allen browne yes/no article. I actually already created new fields that are not yes/no but then realized that the yes/no does work from sql to access kind of so decided to try it out before i converted over to new fields. There are several queries and forms I would have to update if I moved away from the yes/no.

I may end up moving to them and then using some kind of display over time but for now your suggestion of right click just might do the trick.