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?