[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

yes/no fields not working in sql

Posted on 2007-08-06
4
Medium Priority
?
573 Views
Last Modified: 2008-01-09
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]![chkNCRC]=Yes,Yes,Null)
They worked great before.  How can I get them working now that my db is in sql?
Thanks,
Karen
0
Comment
Question by:ksilvoso
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19637725
you will have to use the -1 (true) and 0 (false) explicitely:
IIf([Forms]![FrmMain]![chkNCRC]=-1,Yes,Null)
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 19637729
Make sure your Access checkboxes do NOT have the Triple State property set to Yes, which allows Yes, No, and NULL.
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.
0
 
LVL 1

Author Comment

by:ksilvoso
ID: 19640082
angellll,
  I tried that and it still doesn't work.  Should the field type be something other than bit?
0
 
LVL 1

Author Comment

by:ksilvoso
ID: 19642299
I changed it to this and it works now.  Thanks!
IIf([Forms]![FrmMain]![chkNCRC]=-1,1,Null)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question