Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    54

    Unanswered: Order by (boolean) acces-sqlserver

    I have migrated an Access database to SQLserver,

    all seems ok,
    but I found that an "order by" on a boolean Access field, is reverse under
    SQLserver ?
    I had to add "desc" to my code "order by field desc" to retrieve the correct display.

    Could someone confirm this ? I am very surprised.

    Thanks, Pierre.
    Pierre (Pl-Arts)

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Boolean values are stored differently in different systems. The intuitive "1 = true, 0 = false" is very common, but some systems use a more traditional "-1 = True, 0 = false", which I guess simplifies some boolean arithmatic. If you really want to know, I could ask my father, who taught statistics at Indiana University and who specialized in boolean statistics.
    Be aware also, that SQL server does not have a "boolean" datatype. It has a "bit" datatype which can only take the values 1 and 0. MS Access has a true boolean datatype, so perhaps it usess -1 and 0, and this is where the translation error comes in.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by blindman
    MS Access has a true boolean datatype, so perhaps it usess -1 and 0,
    Confirmed. Access does indeed use -1 and 0.

  4. #4
    Join Date
    Jan 2003
    Posts
    54

    Bolean and access

    yes , I understand that if it is -1,
    the order is reverse,
    But, in access database, the value seen is 1 and not -1,
    do you mean that the real value stored is a -1 ?

    Pierre.
    Pierre (Pl-Arts)

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Plarde
    But, in access database, the value seen is 1 and not -1,
    do you mean that the real value stored is a -1 ?
    Hi Pierre
    Don't know where you got the first bit from (try typing ?(Cint(true)) and hit return in the immediate window - the answer is -1) but yes - True in Access is stored as -1.
    Another gotcha is that the SQL Server Bit allows nulls which is an even more significant difference. If you are linking to SQL Server then you need to take account of this - Access throws a wobbly if it encounters a Null Bit.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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