Query returns Null

[Tod]

I guess this question could go in a few places, but I'll
start here.

I have two queries in an Access database. If I run them
from within Access they return values. Okay fine. But I
am using ADO in an Excel workbook to connect to the
Access database and running the queries. Using ADO with
one of the queries works fine. It returns the values in
the Recordset and all is well. But if I use the same code
with the other query, I get NULL in return. The queries
are identical as far as data types, etc. What gives?

tod

Here is a sample of my ADO code:

cn.Open "Driver={Microsoft Access Driver
(*.mdb)};Dbq=C:\MyPath\MyDatabase.mdb;"

With cm
.ActiveConnection = cn
.CommandText = "qryDailyBusinessHours"
.CommandType = adCmdStoredProc
Set pm1 = cm.CreateParameter("Date_Created", adInteger,
adParamInput)
.Parameters.Append pm1
'ReportMonth contains a integer (1 - 12)
pm1.Value = ReportMonth
Set pm2 = cm.CreateParameter("Date_Created", adInteger,
adParamInput)
.Parameters.Append pm2
'ReportYear contains the integer '2004'
pm2.Value = ReportYear
End With

'Return data
rs.Open cm

 

[Gary Walter]

Hi Tod,

Off hand, it looks like you are using
"Date_Created" twice which does not
make sense to me (unless a typo?).

Would you mind posting back the SQL for
"qryDailyBusinessHours" here so we
can see what the name of your parameters
are? Otherwise, we would probably just be
wasting your time guessing.

If your query is expecting 2 integer parameters,
one for a "ReportMonth"
and another for a "ReportYear"
(in that order),
and ReportMonth/ReportYear are integer variables
assigned values in your code,
then we don't really have to know the parameter
names if we pass them using an array.

Dim cn As New ADODB.Connection
Dim cm As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim ReportMonth As Integer
Dim ReportYear As Integer

ReportMonth = 1
ReportYear = 2004

' Open the connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyPath\MyDatabase.mdb;"

'create the command
Set cm.ActiveConnection = cn
cm.CommandText = "qryDailyBusinessHours"

'execute the command, passing values for parameters in array
Set rs = cm.Execute(, Array(ReportMonth, ReportYear), adCmdStoredProc)

'<do something with rs>

rs.Close
Set rs = Nothing

-------------------

My *guess* is that you might actually
only have one parameter "Date_Created,"
but you want to use your 2 variables.

Any further suggestions would require looking
at the SQL of your query.

Thanks,

Gary Walter

 

[Tod]

Thanx for the reply. I was afraid to give too much code
for fear of overload. But since you asked: ;>

Here's some more detail on what I'm trying to do. I'll
start with the query that works.

In Access, it is set up with five fields:

Group_Name Group
Success Sum
Failure Sum
date_resolve Where Month([date_resolve])=ReportMonth
date_resolve Where Year([date_resolve])=ReportYear

The first field is the one that all the others are
grouped on. The next two are sums. The last two are where
the parameters are entered. The field is listed twice
because Access shows it that way. I initially entered it
like this:

date_resolve Where Month([date_resolve])=ReportMonth
and Year([date_resolve])=ReportYear

But when I save it and close it, then reopen it in design
view, Access has split the field in to two. So I went
with it.

So if I run that query in Access, it'll prompt for the
ReportMonth and ReportYear. I enter those (8 and 2004 for
example) and the resulting dataset contains all data for
August 2004.

Now the queries whole raison d'etre is to supply data for
an Excel report. So in Excel I have VBA code that uses
ADO to connect to the database, supply the ReportMonth
and ReportYear, run the query, and return the results in
a recordset. My code is this:

cn.Open "Driver={Microsoft Access Driver
(*.mdb)};Dbq=C:\MyPath\MyDatabase.mdb;"

'cm is my Command object
'cn is my Connection object
'pm1 and pm2 are my parameters
'rs is my Recordset
'qryDailyBusinessHours is the query I just described
'ReportMonth is an integer variable containing 8
'ReportYear is an integer variable containing 2004
With cm
.ActiveConnection = cn
.CommandText= "qryDailyBusinessHours"
.CommandType = adCmdStoredProc
Set pm1 = cm.CreateParameter("date_resolve", adInteger,
adParamInput)
.Parameters.Append pm1
pm1.Value = ReportMonth
Set pm2 = cm.CreateParameter(InputField, adInteger,
adParamInput)
.Parameters.Append pm2
pm2.Value = ReportYear
End With

rs.Open cm
'Code to use theses results
rs.close
cn.close
set rs = nothing
set cn = nothing
set cm = nothing
set pm1 = nohting
set pm2 = nothing

This works.

Okay, still reading?

I have another access query that is virtually identical.
It has a group by field, two sum fields and the same
date_value fields described at top. It connects to the
same table for data. If I run it in Access, enter the
ReportMonth and ReportYear, I get a dataset with values.
So in my VBA, I have a second procedure that uses the
same exact ADO code mentioned above with the only change
being the query name.

It should work, but it doesn't. It returns NULL for each
field.

I've tried not destroying objects, running the good
procedure first, then the bad one, then in reverse order.
I still get NULL for the 'bad' one and correct results
for the 'good' one.

The good one ALWAYS works and the bad one ALWAYS DOESN'T.
So I'm just scratching my head trying to figure out what
one's got that the other doesn't.

Any ideas would be welcomed. I'll also post a solution if
I come up with it.


Stumped
(A/K/A tod)

 

[Gary Walter]

Hi Tod,

I was hoping you would open your
query in Access Query Designer,
change to SQL View, copy the SQL
stmt there, then paste it in a reply back
to this thread.

It sounds like it might be like:

SELECT
Group_Name,
Sum([Success]) As SumOfSuccess,
Sum([Failure]) As SumOfFailure
FROM yourtable
Where
Month([date_resolve])=[ReportMonth]
AND
Year([date_resolve])= [ReportYear]
GROUP BY
Group_Name;

This would sum Success and Failure
over each Group_Name for the month
and year entered at the parameter prompts.

So the parameter *names* are "ReportMonth"
and "ReportYear" in the query.

Do I have this right so far?

In your code, it sounds like you have determined
values that you want for these parameters and
they are stored in two variables.

Let's give the variables different names from the
parameter names to avoid confusion.

Dim intReportMonth As Integer
Dim intReportYear As Integer

Just so we are on the same page, I am
going to give them values in 2 lines of code.

intReportMonth = 1
intReportYear = 2004

So now you want to pass those values
to the parameters in your query in code.

After you have defined the Command object "cm"

---One way:

'Specify the parameter values
cm.Parameters("ReportMonth") = intReportMonth
cm.Parameters("ReportYear") = intReportYear

NOTE: what goes in between quotes is *name*
of parameter(s) in your query.

---the other way I already showed but we now have
defined separate names for parameters and variables,
so...

'create the command
Set cm.ActiveConnection = cn
cm.CommandText = "qryDailyBusinessHours"

'execute command, passing values for parameters in array
Set rs = cm.Execute(, Array(intReportMonth, intReportYear), adCmdStoredProc)

'<do something with rs>

rs.Close
Set rs = Nothing

Does that help?

Gary Walter

Thanx for the reply. I was afraid to give too much code
for fear of overload. But since you asked: ;>

Here's some more detail on what I'm trying to do. I'll
start with the query that works.

In Access, it is set up with five fields:

Group_Name Group
Success Sum
Failure Sum
date_resolve Where Month([date_resolve])=ReportMonth
date_resolve Where Year([date_resolve])=ReportYear

The first field is the one that all the others are
grouped on. The next two are sums. The last two are where
the parameters are entered. The field is listed twice
because Access shows it that way. I initially entered it
like this:

date_resolve Where Month([date_resolve])=ReportMonth
and Year([date_resolve])=ReportYear

But when I save it and close it, then reopen it in design
view, Access has split the field in to two. So I went
with it.

So if I run that query in Access, it'll prompt for the
ReportMonth and ReportYear. I enter those (8 and 2004 for
example) and the resulting dataset contains all data for
August 2004.

Now the queries whole raison d'etre is to supply data for
an Excel report. So in Excel I have VBA code that uses
ADO to connect to the database, supply the ReportMonth
and ReportYear, run the query, and return the results in
a recordset. My code is this:

cn.Open "Driver={Microsoft Access Driver
(*.mdb)};Dbq=C:\MyPath\MyDatabase.mdb;"

'cm is my Command object
'cn is my Connection object
'pm1 and pm2 are my parameters
'rs is my Recordset
'qryDailyBusinessHours is the query I just described
'ReportMonth is an integer variable containing 8
'ReportYear is an integer variable containing 2004
With cm
.ActiveConnection = cn
.CommandText= "qryDailyBusinessHours"
.CommandType = adCmdStoredProc
Set pm1 = cm.CreateParameter("date_resolve", adInteger,
adParamInput)
.Parameters.Append pm1
pm1.Value = ReportMonth
Set pm2 = cm.CreateParameter(InputField, adInteger,
adParamInput)
.Parameters.Append pm2
pm2.Value = ReportYear
End With

rs.Open cm
'Code to use theses results
rs.close
cn.close
set rs = nothing
set cn = nothing
set cm = nothing
set pm1 = nohting
set pm2 = nothing

This works.

Okay, still reading?

I have another access query that is virtually identical.
It has a group by field, two sum fields and the same
date_value fields described at top. It connects to the
same table for data. If I run it in Access, enter the
ReportMonth and ReportYear, I get a dataset with values.
So in my VBA, I have a second procedure that uses the
same exact ADO code mentioned above with the only change
being the query name.

It should work, but it doesn't. It returns NULL for each
field.

I've tried not destroying objects, running the good
procedure first, then the bad one, then in reverse order.
I still get NULL for the 'bad' one and correct results
for the 'good' one.

The good one ALWAYS works and the bad one ALWAYS DOESN'T.
So I'm just scratching my head trying to figure out what
one's got that the other doesn't.

Any ideas would be welcomed. I'll also post a solution if
I come up with it.


Stumped
(A/K/A tod)

-----Original Message-----
Hi Tod,

Off hand, it looks like you are using
"Date_Created" twice which does not
make sense to me (unless a typo?).

Would you mind posting back the SQL for
"qryDailyBusinessHours" here so we
can see what the name of your parameters
are? Otherwise, we would probably just be
wasting your time guessing.

If your query is expecting 2 integer parameters,
one for a "ReportMonth"
and another for a "ReportYear"
(in that order),
and ReportMonth/ReportYear are integer variables
assigned values in your code,
then we don't really have to know the parameter
names if we pass them using an array.

Dim cn As New ADODB.Connection
Dim cm As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim ReportMonth As Integer
Dim ReportYear As Integer

ReportMonth = 1
ReportYear = 2004

' Open the connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyPath\MyDatabase.mdb;"

'create the command
Set cm.ActiveConnection = cn
cm.CommandText = "qryDailyBusinessHours"

'execute the command, passing values for parameters in array
Set rs = cm.Execute(, Array(ReportMonth, ReportYear), adCmdStoredProc)

'<do something with rs>

rs.Close
Set rs = Nothing

-------------------

My *guess* is that you might actually
only have one parameter "Date_Created,"
but you want to use your 2 variables.

Any further suggestions would require looking
at the SQL of your query.

Thanks,

Gary Walter




.