Access sql 'or' returns true/false not numeric

Question

In Access Sql 1 or 2 returns -1 the value for true. How can i get it to return 3 which is the numeric orring (?) of the values. This works in Sql Server

select 1 | 2

returns 3

Answer 1

Try BOR maybe that will work, http://www.pcreview.co.uk/forums/thread-1177792.php

Answer 2

Just to add to CRice answer, BOR is what is required here.

Here's a repro using Jet 4.0, its OLE DB provider and ADO in VBA code:

Sub TestBOR()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"
    With .ActiveConnection

      Dim Sql As String
      Sql = "SELECT 1 BOR 2;"
      .Execute Sql
      Dim rs
      Set rs = .Execute(Sql)
      MsgBox rs.GetString
    End With
    Set .ActiveConnection = Nothing
  End With
End Sub

The result is 3, as expected.

If you want to do this using the Access UI you need to ensure it is in ANSI-92 Query Mode. If you want to do this using DAO... you can't! DAO always uses ANSI-89 Query Mode. Use ADO because it always uses ANSI-92 Query Mode. Happily, you can mix ADO code with DAO code in the same project.