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.

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;"


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Ron" <(E-Mail Removed)> wrote in message
news:F6FAE141-523A-4490-B9CF-(E-Mail Removed)...
>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.

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.

"Ron" wrote:

> 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.

On Sat, 31 Jan 2009 15:44:01 -0800, Jim Burke in Novi
<(E-Mail Removed)> wrote:

>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.
--

John W. Vinson [MVP]

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

"John W. Vinson" wrote:

> On Sat, 31 Jan 2009 15:44:01 -0800, Jim Burke in Novi
> <(E-Mail Removed)> wrote:
>
> >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.
> --
>
> John W. Vinson [MVP]
>
>