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.


"onedaywhen" wrote:

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.

--