Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: Best Yes / No type field in Server Table

    Hi,

    I am new to SQL Server. I recently migrated my MS Access tables on SQL Server 2005.

    Almost field type I can play with. But the problem arises when Check mark field in MS Access table converted to BIT field when the table was shifted to Server. My Field name is Access was Confirm (Yes / No type). I believe this word is reserve word at Server hence I change it to TConfirm in server table.

    Below was runing smoothly in Access but not with server table.

    Set rst1 = CurrentDb.OpenRecordset("Select * from T_SalesInvFoot " & _
    "Where InvNum = " & Forms!F_SalesInvHead!InvNum & _
    "And TConfirm = 0")

    Can someone let me know what is the best field type for Yes / No type field in SQL Server 2005?

    Thanks
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Aug 2009
    Posts
    262
    it varies upon your need .

    best field type for Yes / No type field in SQL Server 2005

    here is the google result , may u learn what u desire

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes\No is just another word for Boolean in Access. Boolean is another name for Bit. Bit and Yes\ No are logically the same. The only issue is that Booleans in Access can be NULL, 0 or -1. In SQL Server they can be NULL, 0 or 1. However, Access automatically interprets any non-zero value to be -1 when comparing to booleans.

    "Not running smoothly" doesn't really help us - you need to specifically tell us what the problem is.

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by pootle flump View Post
    Boolean is another name for Bit.
    It is not. Bit in SQL Server is a numeric type, which is a fact that a lot of people overlook. So boolean operators like AND, OR are not valid for bits but numeric operators like > and < are valid.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In JET (the default RDBMS for Access), the JET YESNO data type is a synonym for the JET Boolean data type. BIT is another JET synonym of the Boolean datatype. Now I was about to say that Boolean in JET is numeric. However, I'm not certain.

    Code:
    SELECT yn
    FROM thetable
    WHERE yn
    in JET returns all rows with non-zero (or false) values for the boolean yn column.
    Code:
    SELECT num
    FROM thetable
    WHERE num
    in JET returns all rows with non-zero values for the numeric num.

    It also coerces numeric to boolean.
    Code:
    SELECT yn
    FROM thetable
    WHERE yn <  false
    returns all true, or minus one, values.

    My position was that my understanding is that Booleans and numerics in Access can be treated as numerics or booleans. You can, for example, sum Access booleans. My post was limited to Access compared to SQL Server - I suppose you could say I was inaccurate in so far as what I was describing was really a result of Access not being type safe rather than truly reflective of the difference between Bit and Boolean.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Another way of putting it is you are absolutely correct, but Access doesn't care and will treat them both the same.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •