SQL Linked table: -1 does not equal True

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Steven Kogan
    Recognized Expert New Member
    • Jul 2010
    • 107

    SQL Linked table: -1 does not equal True

    I notice on my installation that for linked SQL Server tables, -1 does not evaluate the same as true.

    Is that correct, or is there a patch that fixes that?

    I've tried on 2 systems: one with Access 2003 and another with Access 2010.

    When I Google the topic I can't seem to get confirmation.

    This kind of code seems to always result in rst.EOF=True if referring to a linked SQL Server table:

    Code:
    Public Function TestMinus1()
        Dim sql As String
        Dim rst As DAO.Recordset
        Dim db As DAO.Database
        
        Set db = CurrentDb
        
            
        sql = "SELECT MyBlnField FROM MyTable WHERE MyBlnField=-1"
        
        Set rst = db.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)
        
        If rst.EOF Then
            Debug.Print "Nothing returned"
        End If
        
    End Function
  • zmbd
    Recognized Expert Moderator Expert
    • Mar 2012
    • 5501

    #2
    you didn't move to the first record.
    you didn't check to see if any records were returned.

    insert line 12
    Code:
    rst.movefirst
    if rst.count then
    insert line 16
    Code:
     end if

    also, your question doesn't make sense.
    At the start you are asking about "-1" == True
    Then you switch to dealing with the end of the recordset - these have very little to do with each other outside of the returned record(s).
    Last edited by zmbd; Oct 4 '13, 07:34 PM.

    Comment

    • Steven Kogan
      Recognized Expert New Member
      • Jul 2010
      • 107

      #3
      rst.MoveFirst gives error 3021, no current record.

      rst.Count gives method or data member not found.

      With an actual linked SQL Server table table changing -1 to True returns records.

      Comment

      • Rabbit
        Recognized Expert MVP
        • Jan 2007
        • 12517

        #4
        SQL Server has no data type of boolean. Most likely it is a bit field and the Access SQL parser is making the conversion of the keywords true/false into a bit field. A bit field has only two possible values, 0 and 1.

        Comment

        • Steven Kogan
          Recognized Expert New Member
          • Jul 2010
          • 107

          #5
          I agree with you. It is actually a bit field.

          When viewed as a datasheet the values display as 0 and -1. If you use -1 as criteria it returns no records. If you use 1 as criteria it returns records, but displays the values as -1. If you use True as criteria it also returns the records that display as -1.

          At least those are my results. I'm not sure if it is my installation.
          Last edited by Steven Kogan; Oct 4 '13, 08:37 PM. Reason: meant bit

          Comment

          • Rabbit
            Recognized Expert MVP
            • Jan 2007
            • 12517

            #6
            It's how Access is interpreting the data types from SQL Server to Access. The value on SQL Server is 1. But Access is interpreting it as a boolean, making the display conversion to -1.

            Comment

            • Steven Kogan
              Recognized Expert New Member
              • Jul 2010
              • 107

              #7
              That seems to be what's happening. It displays -1, but the underlying value is 1. It resolves to True and to 1, but not to -1. For an Access table you can code -1 as if it is True, but for linked tables -1 is not the same as true.

              I'd be interested to hear if others get the same result, or if a later patch fixes it so that using -1 as criteria on a linked SQL Server table returns 'True' results (which is actually equal to 1 in the underlying data, but is displayed as -1).

              Comment

              • Steven Kogan
                Recognized Expert New Member
                • Jul 2010
                • 107

                #8
                Here's something odd.

                Code:
                SELECT Val(MyBlnField) FROM MyTable WHERE Val(MyBlnField)=-1
                Returns records, and if the field is True, Val(MyBlnField) evaluates to -1.

                So Access is converting the SQL bit value = 1 to -1, but not in all cases. At least that's what I'm finding...

                Comment

                • Rabbit
                  Recognized Expert MVP
                  • Jan 2007
                  • 12517

                  #9
                  Val() is an Access function. Val() will therefore take the SQL Server data and convert it to a data type that Access can read, ie boolean, ie -1.

                  Access is not ANSI SQL compliant. It's not a simple matter of patching Access as it would be rewriting the SQL engine that that underlies Access. I wouldn't hold your breath on a patch for this.

                  Comment

                  Working...