Switch() causes strange ADO result

[onedaywhen]

Can anyone explain this?

Sub test()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
..ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\New_Jet_DB.mdb"
..Source = "SELECT Switch(1=1, MyIntegerCol, 0=1, NULL) FROM MyTable"
..Open
MsgBox .Fields(0).Value
MsgBox .Fields(0).Type = adVarBinary
End With
End Sub

Test table:

CREATE TABLE MyTable (
MyIntegerCol INTEGER NOT NULL
)
;
INSERT INTO MyTable VALUES (2)
;

Many thanks,
Jamie.

--

 

[Guest]

I have never used 'switch' in SQL: I would use Case When. However, I can see
what the 'Switch' might be intended for.

I believe the problem is that the record set does not return 2, as expected.
When I tried:

SELECT Switch(1=1, MyIntegerCol, 0=1,90) FROM MyTable

the recordset does return 2.
Therefore, I would GUESS that it is not possible to specify a missing value
using the word NULL.

 

[Lynn Trapp]

Apparently, Jet is confused about the datatype. Try modifying your .Source
argument like this:

..Source = "SELECT CInt(Switch(1=1, MyIntegerCol, 0=1, NULL)) FROM MyTable"

 

[onedaywhen]

I have never used 'switch' in SQL: I would use Case When.

With Jet? AFAIK it is not supported.

Therefore, I would GUESS that it is not possible to specify a missing value
using the word NULL.

Because the expression 0=1 can never be true it should never actually
return NULL. I guess in common with the IIF function, all clauses are
evaluated regardless.

Thanks,
Jamie.

--

 

[onedaywhen]

Apparently, Jet is confused about the datatype. Try modifying your
.Source argument

Thanks for suggesting a workaround. However, the query is merely
academic. I'm interested in source of the problem or at least why the
result is of type adVarBinary.

Thanks again,
Jamie.

--

 

[Lynn Trapp]

Thanks for suggesting a workaround. However, the query is merely

academic. I'm interested in source of the problem or at least why the
result is of type adVarBinary.

Well, it is quite likely a bug in Jet. Several of the MVPs have discussed it
among ourselves and we don't really know what the source of it is, unless it
is a bug.

 

[onedaywhen]

Lynn, Thanks for your reply:

Several of the MVPs have discussed it
among ourselves

Really? Has this issue come up before?

Jamie.

--

 

[Lynn Trapp]

Not that I know of. Someone saw your post in the excel newsgroup and several
of us started looking into it.

 

[Michel Walsh]

Hi,


Works fine without explicit recordset, both in ADO and in DAO:


----------------
CurrentDb.Execute "CREATE TABLE MyTable (MyIntegerCol INTEGER NOT NULL)"
CurrentDb.Execute "INSERT INTO MyTable VALUES (2)"
? CurrentDb.OpenRecordset("SELECT Switch(1=1, myintegerCol, 0=1, Null) FROM
MyTable").Fields(0).Value
2
? CurrentProject.Connection.Execute("SELECT Switch(1=1, myintegerCol, 0=1,
Null) FROM MyTable").Fields(0).Value
2
-----------------

It also works fine within Access:


---------------
Public Sub NullSwitch()
Dim rst As New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Switch(1=1, MyIntegerCol, 0=1, NULL) FROM MyTable"
.Open

Debug.Print .Fields(0).Value
End With
End Sub
-------------
returns 2 in the immediate debug window.


Is it possible you didn't create the table into ANOTHER database (or in a db
different than the one you open with the specified connection) ? and that
in your C:\Tempo\New_Jet_DB.mdb, the table MyTable (quite common name)
already has a record where MyIntegerCol ... is null.






Vanderghast, Access MVP

 

[onedaywhen]

it is quite likely a bug in Jet.

More likely a bug in the *provider*. It works as expected in the MS
Access UI.

Jamie.

--

 

[onedaywhen]

Works fine without explicit recordset, both in ADO

and in DAO

It makes no difference for me. You don't reveal you connection string
but from the DAO comparison I suspect you are using the 3.51 OLE DB
provider (my code as posted uses the 4.0 provider). Please confirm.
Thanks again,
Jamie.

--