Strange Query/VBA behavior

[JK]

Having a lot of trouble with this...

What I want to do is to concatenate the results of a query into a
string to display on an Access report. I thought - easy enough, I'll
write a quick function that opens the query in a Recordset object,
cycles through each record adding record information to a string
variable, and then return the string. The problem is that opening the
recordset returns zero records.

Here's my code:

Dim oRs As ADODB.Recordset
Dim oConn As ADODB.Connection
Dim strSQL As String
Dim strOutputString As String

Set oRs = New ADODB.Recordset
Set oConn = CurrentProject.Connection

strSQL = "SELECT * FROM qryBudgetApprovedSummary " & _
"WHERE FY LIKE '" & [Forms]![frmReportSelector]![txtFY] &
"' " & _
"AND Country LIKE '" &
[Forms]![frmReportSelector]![txtCountry] & "' " & _
"AND Country <> 'Operations Support' AND Country <>
'Transfer' " & _
"AND Team LIKE '" & [Forms]![frmReportSelector]![txtTeam] &
"' " & _
"AND FundAccount NOT LIKE 'TI' " & _
"AND SumOfBudgetAmount <> 0;"
oRs.Open strSQL, oConn, adOpenStatic, adLockReadOnly
strOutputString = ""
Do While Not oRs.EOF
strOutputString = strOutputString & oRs("Country") & ": " &
oRs("FundAccount") & " " & oRs("SumOfBudgetAmount") & "; "
oRs.MoveNext
Loop

When I run this code and step into it, it skips right over my
Do...Loop. When I put in a couple of Debug.Prints, I get a 0 for
oRs.RecordCount, and the following as strSQL:

SELECT * FROM qryBudgetApprovedSummary WHERE FY LIKE '*2007*' AND
Country LIKE '*' AND Country <> 'Operations Support' AND Country <>
'Transfer' AND Team LIKE '*' AND SumOfBudgetAmount <> 0;

Here's the kicker - if I copy and paste this *exact* select statement
into a Access query object, it returns the results I expect.

Any ideas out there on what I'm doing wrong OR what I have to change to
get around an Access oddity???

Thanks in advance!
JK

 

[Cinzia]

Having a lot of trouble with this...

What I want to do is to concatenate the results of a query into a
string to display on an Access report. I thought - easy enough, I'll
write a quick function that opens the query in a Recordset object,
cycles through each record adding record information to a string
variable, and then return the string. The problem is that opening the
recordset returns zero records.

Here's my code:

Dim oRs As ADODB.Recordset
Dim oConn As ADODB.Connection
Dim strSQL As String
Dim strOutputString As String

Set oRs = New ADODB.Recordset
Set oConn = CurrentProject.Connection

strSQL = "SELECT * FROM qryBudgetApprovedSummary " & _
"WHERE FY LIKE '" & [Forms]![frmReportSelector]![txtFY] &
"' " & _
"AND Country LIKE '" &
[Forms]![frmReportSelector]![txtCountry] & "' " & _
"AND Country <> 'Operations Support' AND Country <>
'Transfer' " & _
"AND Team LIKE '" & [Forms]![frmReportSelector]![txtTeam]
& "' " & _
"AND FundAccount NOT LIKE 'TI' " & _
"AND SumOfBudgetAmount <> 0;"
oRs.Open strSQL, oConn, adOpenStatic, adLockReadOnly
strOutputString = ""
Do While Not oRs.EOF
strOutputString = strOutputString & oRs("Country") & ": " &
oRs("FundAccount") & " " & oRs("SumOfBudgetAmount") & "; "
oRs.MoveNext
Loop

When I run this code and step into it, it skips right over my
Do...Loop. When I put in a couple of Debug.Prints, I get a 0 for
oRs.RecordCount, and the following as strSQL:

SELECT * FROM qryBudgetApprovedSummary WHERE FY LIKE '*2007*' AND
Country LIKE '*' AND Country <> 'Operations Support' AND Country <>
'Transfer' AND Team LIKE '*' AND SumOfBudgetAmount <> 0;

Here's the kicker - if I copy and paste this *exact* select statement
into a Access query object, it returns the results I expect.

Any ideas out there on what I'm doing wrong OR what I have to change
to get around an Access oddity???

Thanks in advance!
JK

Hi JK,
with ADO the wildcard Operator is % and Not * used by DAO recordset, try to
change Like '*' into Like '%'


Bye

 

[JK]

So simple, yet so absolutely correct. Thank you!!!!

Having a lot of trouble with this...

What I want to do is to concatenate the results of a query into a
string to display on an Access report. I thought - easy enough, I'll
write a quick function that opens the query in a Recordset object,
cycles through each record adding record information to a string
variable, and then return the string. The problem is that opening the
recordset returns zero records.

Here's my code:

Dim oRs As ADODB.Recordset
Dim oConn As ADODB.Connection
Dim strSQL As String
Dim strOutputString As String

Set oRs = New ADODB.Recordset
Set oConn = CurrentProject.Connection

strSQL = "SELECT * FROM qryBudgetApprovedSummary " & _
"WHERE FY LIKE '" & [Forms]![frmReportSelector]![txtFY] &
"' " & _
"AND Country LIKE '" &
[Forms]![frmReportSelector]![txtCountry] & "' " & _
"AND Country <> 'Operations Support' AND Country <>
'Transfer' " & _
"AND Team LIKE '" & [Forms]![frmReportSelector]![txtTeam]
& "' " & _
"AND FundAccount NOT LIKE 'TI' " & _
"AND SumOfBudgetAmount <> 0;"
oRs.Open strSQL, oConn, adOpenStatic, adLockReadOnly
strOutputString = ""
Do While Not oRs.EOF
strOutputString = strOutputString & oRs("Country") & ": " &
oRs("FundAccount") & " " & oRs("SumOfBudgetAmount") & "; "
oRs.MoveNext
Loop

When I run this code and step into it, it skips right over my
Do...Loop. When I put in a couple of Debug.Prints, I get a 0 for
oRs.RecordCount, and the following as strSQL:

SELECT * FROM qryBudgetApprovedSummary WHERE FY LIKE '*2007*' AND
Country LIKE '*' AND Country <> 'Operations Support' AND Country <>
'Transfer' AND Team LIKE '*' AND SumOfBudgetAmount <> 0;

Here's the kicker - if I copy and paste this *exact* select statement
into a Access query object, it returns the results I expect.

Any ideas out there on what I'm doing wrong OR what I have to change
to get around an Access oddity???

Thanks in advance!
JK

Hi JK,
with ADO the wildcard Operator is % and Not * used by DAO recordset, try to
change Like '*' into Like '%'


Bye