DAO query runs and connects but retrieves zero records?

[Guest]

Hello, I'm using Access 2002. The following DAO query runs, connects, and does not generate any errors, but it also fails to retrieve any records. Could someone clue me in on where I'm going wrong, and fix the following code
By the way, the connection is to an Oracle DB

Dim ws As DAO.Workspac
Dim cn As DAO.Connectio
Dim rs As DAO.Recordse
Dim strConnect As Strin

strConnect = DAOConnection(1
Set ws = CreateWorkspace("", "", "", dbUseODBC
Set cn = ws.OpenConnection("", , , strConnect
cn.QueryTimeout = 33
Set rs = cn.OpenRecordset(argSQL, dbOpenDynaset
rs.MoveLas

Thanks in advance for your assistance.

 

[Roger Carlson]

argSQL is a variable that should hold a SQL statement like:
"Select * from Sometable"

I don't see where you have assigned that variable ANY value. If you have
not, is it any wonder no records are being returned?

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Hello, I'm using Access 2002. The following DAO query runs, connects, and

does not generate any errors, but it also fails to retrieve any records.
Could someone clue me in on where I'm going wrong, and fix the following
code?

 

[Guest]

Sorry, "argSQL" is an argument supplied by a calling subroutine. Yes, "argSQL" is assigned good SQL.

 

[Douglas J. Steele]

It might help it you showed us the SQL that fails to retrieve any records.
Perhaps it isn't proper SQL for DAO...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry, "argSQL" is an argument supplied by a calling subroutine. Yes,

"argSQL" is assigned good SQL.

 

[Guest]

Hi Douglas, here is my SQL, it may be easier to read if copied into a module and the line wrapping corrected?

Dim strSQL As String

strSQL = "SELECT "
strSQL = strSQL & "A.ACCTNO, "
strSQL = strSQL & "(A.BUDGET_BEGIN_DR-A.BUDGET_BEGIN_CR + A.BUDGET_PER_DR-A.BUDGET_PER_CR) BUDGET, "
strSQL = strSQL & "(A.ENCUMB_BEGIN_DR-A.ENCUMB_BEGIN_CR + A.ENCUMB_PER_DR-A.ENCUMB_PER_CR) Encumbrance, "
strSQL = strSQL & "(A.ACTUAL_BEGIN_DR-A.ACTUAL_BEGIN_CR + A.ACTUAL_PER_DR-A.ACTUAL_PER_CR) Actual, "
strSQL = strSQL & "DECODE(A.TYPE, " 'If account type is...
strSQL = strSQL & "'A', '-n/a-', " 'Asset, then enter "Null"
strSQL = strSQL & "'L', '-n/a-', " 'Liability, then "Null"
strSQL = strSQL & "'O', '-n/a-', " 'Equity, then "Null"
strSQL = strSQL & "'R', ((A.BUDGET_BEGIN_CR-A.BUDGET_BEGIN_DR + A.BUDGET_PER_CR-A.BUDGET_PER_DR)-(A.ENCUMB_BEGIN_CR-A.ENCUMB_BEGIN_DR + A.ENCUMB_PER_CR-A.ENCUMB_PER_DR)-(A.ACTUAL_BEGIN_CR-A.ACTUAL_BEGIN_DR + A.ACTUAL_PER_CR-A.ACTUAL_PER_DR)), "
strSQL = strSQL & "'E', ((A.BUDGET_BEGIN_DR-A.BUDGET_BEGIN_CR + A.BUDGET_PER_DR-A.BUDGET_PER_CR)-(A.ENCUMB_BEGIN_DR-A.ENCUMB_BEGIN_CR + A.ENCUMB_PER_DR-A.ENCUMB_PER_CR)-(A.ACTUAL_BEGIN_DR-A.ACTUAL_BEGIN_CR + A.ACTUAL_PER_DR-A.ACTUAL_PER_CR))) AVAIL, "
strSQL = strSQL & "SUBSTR(A.PERIOD_NAME, 1, 3)||'-'||DECODE(SUBSTR(A.PERIOD_NAME, 1, 3), 'Oct', A.PERIOD_YEAR - 1, 'Nov', A.PERIOD_YEAR - 1, 'Dec', A.PERIOD_YEAR - 1, A.PERIOD_YEAR) PERIOD, "
strSQL = strSQL & "A.TYPE T, "
strSQL = strSQL & "A.FUND_DESC, "
strSQL = strSQL & "A.CENTER_DESC, "
strSQL = strSQL & "A.ACCOUNT_DESC, "
strSQL = strSQL & "A.PROJECT_DESC, "
strSQL = strSQL & "A.ACTIVITY_DESC, "
strSQL = strSQL & "A.UNIT_DESC, "
strSQL = strSQL & "A.FERC_DESC, "
strSQL = strSQL & "A.ENABLED, "
strSQL = strSQL & "A.SUMMARY, "
strSQL = strSQL & "A.CODE_COMBINATION_ID, "
strSQL = strSQL & "TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI AM') RETRIEVED "
strSQL = strSQL & "FROM "
strSQL = strSQL & "APPS.GLBALVW A "
strSQL = strSQL & "WHERE "
'Sql = Sql & "A.ACCTNO IN (" & mAcctString & ") AND "
strSQL = strSQL & "A.FUND = '500' AND "
strSQL = strSQL & "((A.BUDGET_BEGIN_DR-A.BUDGET_BEGIN_CR+A.BUDGET_PER_DR-A.BUDGET_PER_CR) <> '0' OR "
strSQL = strSQL & "(A.ENCUMB_BEGIN_DR-A.ENCUMB_BEGIN_CR+A.ENCUMB_PER_DR-A.ENCUMB_PER_CR) <> '0' OR "
strSQL = strSQL & "(A.ACTUAL_BEGIN_DR-A.ACTUAL_BEGIN_CR+A.ACTUAL_PER_DR-A.ACTUAL_PER_CR) <> '0') AND "
strSQL = strSQL & "A.PERIOD_NAME = '" & Format(Now(), "MMM-YY") & "' "
strSQL = strSQL & "ORDER BY DECODE(A.TYPE, 'A', 'A', 'L', 'B', 'O', 'C', 'R', 'D', 'E', 'F')||A.FUND||A.CENTER||A.ACCOUNT||A.PROJECT||A.ACTIVITY||A.BUSINESS_UNIT||A.FERC;"

 

[Douglas J. Steele]

What happens when you run that query in Oracle?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas, here is my SQL, it may be easier to read if copied into a

module and the line wrapping corrected?

Dim strSQL As String

strSQL = "SELECT "
strSQL = strSQL & "A.ACCTNO, "
strSQL = strSQL & "(A.BUDGET_BEGIN_DR-A.BUDGET_BEGIN_CR +

A.BUDGET_PER_DR-A.BUDGET_PER_CR) BUDGET, "

strSQL = strSQL & "(A.ENCUMB_BEGIN_DR-A.ENCUMB_BEGIN_CR +

A.ENCUMB_PER_DR-A.ENCUMB_PER_CR) Encumbrance, "

strSQL = strSQL & "(A.ACTUAL_BEGIN_DR-A.ACTUAL_BEGIN_CR +

A.ACTUAL_PER_DR-A.ACTUAL_PER_CR) Actual, "

strSQL = strSQL & "DECODE(A.TYPE, " 'If account type is...
strSQL = strSQL & "'A', '-n/a-', " 'Asset, then enter "Null"
strSQL = strSQL & "'L', '-n/a-', " 'Liability, then "Null"
strSQL = strSQL & "'O', '-n/a-', " 'Equity, then "Null"
strSQL = strSQL & "'R', ((A.BUDGET_BEGIN_CR-A.BUDGET_BEGIN_DR +

A.BUDGET_PER_CR-A.BUDGET_PER_DR)-(A.ENCUMB_BEGIN_CR-A.ENCUMB_BEGIN_DR +
A.ENCUMB_PER_CR-A.ENCUMB_PER_DR)-(A.ACTUAL_BEGIN_CR-A.ACTUAL_BEGIN_DR +
A.ACTUAL_PER_CR-A.ACTUAL_PER_DR)), "

strSQL = strSQL & "'E', ((A.BUDGET_BEGIN_DR-A.BUDGET_BEGIN_CR +

A.BUDGET_PER_DR-A.BUDGET_PER_CR)-(A.ENCUMB_BEGIN_DR-A.ENCUMB_BEGIN_CR +
A.ENCUMB_PER_DR-A.ENCUMB_PER_CR)-(A.ACTUAL_BEGIN_DR-A.ACTUAL_BEGIN_CR +
A.ACTUAL_PER_DR-A.ACTUAL_PER_CR))) AVAIL, "

strSQL = strSQL & "SUBSTR(A.PERIOD_NAME, 1,

3)||'-'||DECODE(SUBSTR(A.PERIOD_NAME, 1, 3), 'Oct', A.PERIOD_YEAR - 1,
'Nov', A.PERIOD_YEAR - 1, 'Dec', A.PERIOD_YEAR - 1, A.PERIOD_YEAR) PERIOD, "

strSQL = strSQL & "A.TYPE T, "
strSQL = strSQL & "A.FUND_DESC, "
strSQL = strSQL & "A.CENTER_DESC, "
strSQL = strSQL & "A.ACCOUNT_DESC, "
strSQL = strSQL & "A.PROJECT_DESC, "
strSQL = strSQL & "A.ACTIVITY_DESC, "
strSQL = strSQL & "A.UNIT_DESC, "
strSQL = strSQL & "A.FERC_DESC, "
strSQL = strSQL & "A.ENABLED, "
strSQL = strSQL & "A.SUMMARY, "
strSQL = strSQL & "A.CODE_COMBINATION_ID, "
strSQL = strSQL & "TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI AM') RETRIEVED "
strSQL = strSQL & "FROM "
strSQL = strSQL & "APPS.GLBALVW A "
strSQL = strSQL & "WHERE "
'Sql = Sql & "A.ACCTNO IN (" & mAcctString & ") AND "
strSQL = strSQL & "A.FUND = '500' AND "
strSQL = strSQL &

strSQL = strSQL &

strSQL = strSQL &

strSQL = strSQL & "A.PERIOD_NAME = '" & Format(Now(), "MMM-YY") & "' "
strSQL = strSQL & "ORDER BY DECODE(A.TYPE, 'A', 'A', 'L', 'B', 'O', 'C',

'R', 'D', 'E',
'F')||A.FUND||A.CENTER||A.ACCOUNT||A.PROJECT||A.ACTIVITY||A.BUSINESS_UNIT||A
..FERC;"