SQL Linked table: -1 does not equal True

[Steven Kogan]

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:

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]

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

insert line 12

rst.movefirst
if rst.count then

insert line 16

 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).

[Steven Kogan]

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.

[Rabbit]

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.

[Steven Kogan]

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.

[Rabbit]

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.

[Steven Kogan]

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).

[Steven Kogan]

Here's something odd.

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...

[Rabbit]

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.