ADO - Retrieve record with InStr criteria

[Ron]

I am trying to pull a record set from our financial software's database. I
only want GL accounts that are associated with a certain location. The GL
Accounts use the sturcture GLCODE-LOCATION. I am using the OLE DB driver to
access the data. Here is a snippet of my code

Const CONNECTION As String = "Provider=PervasiveOLEDB;Data
Source=DB;Location=SERVER;"
Dim CN As ADODB.connection
Dim rsQuery As ADODB.Recordset
Dim rsDetails As ADODB.Recordset
Dim qryDetails As String


'open a connection to the database
Set CN= New ADODB.connection
CN.Open CONNECTION


'Create Query to retrieve transaction details
qryDetails = "SELECT BATCHNBR, ENTRYNBR, ACCTID, TRANSNBR, FISCALPERD,
Sum(TRANSAMT) AS TRANSAMOUNT " & _
"FROM GLPJD " & _
"WHERE BATCHNBR >= '363361' " & _
"GROUP BY BATCHNBR, ENTRYNBR, ACCTID, TRANSNBR, FISCALPERD
" & _
"HAVING InStr(ACCTID, 'FR') <> 0 " & _
"ORDER BY BATCHNBR ASC;"
'"HAVING InStr(ACCTID, 'FR') <> 0 "

Set rsDetails = New ADODB.Recordset
Set rsDetails.ActiveConnection = CN
rsDetails.Source = qryDetails
rsDetails.LockType = adLockReadOnly
rsDetails.CursorType = adOpenKeyset
rsDetails.Open

I am unable to upen the recordset. I get the following error:

-2147467259 -->[LNA][ODBC Engine Interface] Error in expression:
InStr(ACCTID, 'FR').

I have also tried "HAVING CharIndex('FR', ACCTID) <> 0 " and that doesn't
work either. anyone have a solution to my error?

Thanks in advance.

 

[Douglas J. Steele]

You can't use VBA functions when you're running queries against other DBMS.

If you're looking for those accounts that have FR anywhere in them, use LIKE
'%FR%'

Incidentally, you'd be far better off putting that in the WHERE clause
rather than in the HAVING clause. WHERE clauses are evaluated before the
aggregation is done, HAVING clauses are evaluated after. That means your
query may be doing far more accumulation than necessary.

qryDetails = "SELECT BATCHNBR, ENTRYNBR, ACCTID, TRANSNBR, " & _
"FISCALPERD, Sum(TRANSAMT) AS TRANSAMOUNT " & _
"FROM GLPJD " & _
"WHERE BATCHNBR >= '363361' " & _
"AND ACCTID LIKE '%FR%' " & _
"GROUP BY BATCHNBR, ENTRYNBR, ACCTID, TRANSNBR, FISCALPERD " & _
"ORDER BY BATCHNBR ASC;"

 

[Jim Burke in Novi]

What Doug said. As far as I know, you only use HAVING if you need criteria
based on a 'summarized' field, e.g. HAVING SUM(fieldX) > 100, otherwise it
goes in the WHERE clause.

InStr isn't what you'd use here, but just as a note, the first argument in
InStr is the starting position, which you didn't have. I've used InStr in
queries before where I had something like , e.g. IIf(Instr(1,field1,"x"),1,2)
AS MyField, which would create a field called MyField and assign it a value
of 1 if "x" is anywhere in the field, otherwise it would assign a value of 2.

 

[John W. Vinson]

InStr isn't what you'd use here, but just as a note, the first argument in
InStr is the starting position, which you didn't have.

And that parameter *is optional*. I'm not sure how VBA figures it out but

InStr("abcdeabcde", "e")

correctly returns 5, you don't need the 1.

 

[Jim Burke in Novi]

I didn't know that (obviously). Makes sense to default it to 1, since that's
usually what you want.