Switch() causes strange ADO result
 

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.

--

Switch() causes strange ADO result
 

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.

--

Switch() causes strange ADO result
 

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"

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"onedaywhen" wrote in message
ups.com...
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.

--

Switch() causes strange ADO result
 

AA2e72E wrote:

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.

--

Switch() causes strange ADO result
 

Lynn Trapp wrote:

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.

--

Switch() causes strange ADO result
 

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.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Switch() causes strange ADO result
 

Lynn, Thanks for your reply:

Several of the MVPs have discussed it
among ourselves

Really? Has this issue come up before?

Jamie.

--

Switch() causes strange ADO result
 

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

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"onedaywhen" wrote in message
oups.com...
Lynn, Thanks for your reply:

Several of the MVPs have discussed it
among ourselves

Really? Has this issue come up before?

Jamie.

--

Switch() causes strange ADO result
 

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" wrote in message
ups.com...
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.

--

Switch() causes strange ADO result
 

Lynn Trapp wrote:
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.

--

Switch() causes strange ADO result
 

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.

--