ksilvoso
asked on
yes/no fields not working in sql
I had an access database which I have had to move to sql. I am accessing it through an access front end -( .mdb not adp) with an odbc connection. I had alot of fields in my access database which were yes/no fields and my forms had check boxes for them. Since moving my database to sql the values are still there, ex: the yes shows up as -1 and nos show as 0. They are bit datatypes in the sql database and yet still show as yes/no fields inn the access front end. However, my check boxes don't work anymore. Here is the criteria that I had on each check box (with different field names.)
IIf([Forms]![FrmMain]![chk NCRC]=Yes, Yes,Null)
They worked great before. How can I get them working now that my db is in sql?
Thanks,
Karen
IIf([Forms]![FrmMain]![chk
They worked great before. How can I get them working now that my db is in sql?
Thanks,
Karen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
angellll,
I tried that and it still doesn't work. Should the field type be something other than bit?
I tried that and it still doesn't work. Should the field type be something other than bit?
ASKER
I changed it to this and it works now. Thanks!
IIf([Forms]![FrmMain]![chk NCRC]=-1,1 ,Null)
IIf([Forms]![FrmMain]![chk
Then, go into your SQL table, bit column, and run an UPDATE query to convert any NULL values to Yes or No (whichever you prefer)
Last I remember there is a known issue with SQL bit fields, where when you enter a NULL value in a record it locks that table for future editing.