ADO changes my SELECTed data with rs = conn.execute(sql) in MSAccess2000

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

HEllo,

In My Access2000 Module i have:
-------------------------------------------

Set adoConn = CurrentProject.Connection

sql = "SELECT organizationName, (TRIM(LCASE(countryName)) Like 'belg*') AS
isInBelgium FROM organization;"

rs = conn.execute(sql);


The problem is:
------------------------------
When i run the above query in Access, i get:
- '' IF countryName was empty
- '-1' IF countryName was Like 'belg*'
- '0' IF countryName was not Like 'belg*'


However, when i run via ADO like in the above example, i get
- '0' IF countryName was empty
- '1' IF countryName was Like 'belg*'
- '1' IF countryName was not Like 'belg*'


What happens? whay does ado change these values?


Thnx in advance ...
 
Access, JET, and ADO consistently treat unknown values as Null (which was, I
believe, one of E.F. Codd's original 12 rules for relational databases).
ADO, it would appear, does not.

The following change should fix it ...

IIF(countryName IS NULL, '', TRIM(LCASE(countryName)) LIKE 'belg*')
 
thnc for your quick response, but:

I changed my SQL string to your suggestion:
IIF(countryName IS NULL, '', TRIM(LCASE(countryName)) LIKE 'belg*')

and it works from access, but the via adoRs = conn.execute(sql) i still get
equal values for like 'belg*' and not like 'belg*'

Then i tried

Binnenland: (IIf(Trim(LCase(tblLanden.Land)) Like 'bel*';'yeps';'nopes'))

but still all is 'nopes':

true in access = -1 , renders 0 via ado
false in access = 0, renders 0 via ado

?????
Pieter
 
I'm going to do some testing on this, Joe, and I'll post back later.
Meanwhile, I just noticed an error in my previous reply. When I said that
Access, JET and ADO consistently treat unknown values as Null, I meant, of
course, Access, JET and DAO.

Later ...
 
Well, it would appear that I was a little unfair to ADO on this occasion!
:-)

It turns out that ADO is in fact treating the unknown values in the same way
as DAO. There are two changes needed to get the desired result. First, for
ADO, we need to use the ANSI standard wildcard character '%', instead of
'*'.

Second, if we want to get an empty string when the value is Null, we need to
use Trim$() rather than Trim(). They both do the same job of removing
leading or trailing spaces, but Trim$() returns a string, coercing Null
values to empty strings, while Trim returns a variant, so will return Null
if the value was Null.

My test code looks like this ...

Public Sub TestSub()

Dim strSQL As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

strSQL = "SELECT tblTest.TestID, " & _
"tblTest.OrganisationName, " & _
"tblTest.CountryName, " & _
"Trim$(LCase$([CountryName])) Like 'belg%' AS Expr1 " & _
"FROM tblTest " & _
"ORDER BY tblTest.TestID;"

Set cnn = CurrentProject.Connection
Set rst = cnn.Execute(strSQL, , adCmdText)
With rst
Do Until .EOF
Debug.Print .Fields("TestID"), _
.Fields("OrganisationName"), _
.Fields("CountryName"), _
.Fields("Expr1")
.MoveNext
Loop
.Close
End With
Set rst = Nothing
Set cnn = Nothing

End Sub
 
Thnks alot!!!

with a combination of your feedback i got my desired results:

IIF(TRIM(LCASE(tblLanden.Land)) LIKE 'bel%', true, false) AS Binnenland

the wildcard was the key! although it doesnt work on access, only via ado

and using the $ functions in the above statement result in empty values if
tblLanden.Land == ''
that i dont nderstand but anyway, i have the solutoin now ...

by the way, my name is Pieter

cheers!


Brendan Reynolds (MVP) said:
Well, it would appear that I was a little unfair to ADO on this occasion!
:-)

It turns out that ADO is in fact treating the unknown values in the same way
as DAO. There are two changes needed to get the desired result. First, for
ADO, we need to use the ANSI standard wildcard character '%', instead of
'*'.

Second, if we want to get an empty string when the value is Null, we need to
use Trim$() rather than Trim(). They both do the same job of removing
leading or trailing spaces, but Trim$() returns a string, coercing Null
values to empty strings, while Trim returns a variant, so will return Null
if the value was Null.

My test code looks like this ...

Public Sub TestSub()

Dim strSQL As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

strSQL = "SELECT tblTest.TestID, " & _
"tblTest.OrganisationName, " & _
"tblTest.CountryName, " & _
"Trim$(LCase$([CountryName])) Like 'belg%' AS Expr1 " & _
"FROM tblTest " & _
"ORDER BY tblTest.TestID;"

Set cnn = CurrentProject.Connection
Set rst = cnn.Execute(strSQL, , adCmdText)
With rst
Do Until .EOF
Debug.Print .Fields("TestID"), _
.Fields("OrganisationName"), _
.Fields("CountryName"), _
.Fields("Expr1")
.MoveNext
Loop
.Close
End With
Set rst = Nothing
Set cnn = Nothing

End Sub

--
Brendan Reynolds (MVP)
(e-mail address removed)

Brendan Reynolds (MVP) said:
I'm going to do some testing on this, Joe, and I'll post back later.
Meanwhile, I just noticed an error in my previous reply. When I said that
Access, JET and ADO consistently treat unknown values as Null, I meant, of
course, Access, JET and DAO.

Later ...
--
Brendan Reynolds (MVP)
(e-mail address removed)

still
get (which
was,
Click to expand...
Click to expand...
 
Back
Top