Parameter Queries in Reports

[dgunning]

I'm a pretty new Access user, so maybe I'm doing this the hard way. But
nothing I've tried has worked, so I'd really appreciate some help.

I have a report which is based on a parameter query. I can determine the
value of the parameter through code, and so I don't want to fill in a pop-up
box everytime I run the report; I want to establish the value of the
parameter in the reports onOpen event and then open the report using this
value. My code in the onOpen event is:

Private Sub Report_Open(Cancel As Integer)
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set qry = db.QueryDefs("qryWO")
qry.Parameters("EOMDATE").Value = #3/31/2008#
Set rst = qry.OpenRecordset

Me.RecordSource = rst.Name

The literal date above for the parameter value is just an attempt to keep
the code short while I get this working, once this works I will read the
value from a table.

When I run this, the pop up asking for the parameter value still comes up.
I've tried actually removing the parameter from the query, removing the
recordsource from the report. No matter what I do, the user is always
prompted for the parameter. Thanks for any help.

 

[akphidelt]

Try this... im not entirely sure why it works, but it works for me.

For Each prm In qry.Parameters
prm.Value = Eval(prm.Name)
Next

Put this in the place of
qry.Parameters("EOMDATE").Value = #3/31/2008#

 

[Duane Hookom]

Is there a reason why you don't use the Where Condition of the
DoCmd.OpenReport method?

 

[dgunning]

Is there a reason why you don't use the Where Condition of the
DoCmd.OpenReport method?

I did try that, but wasn't able to get that to work either. My selection
query is (to me) kind of complex, but I have the query working as it should,
so it seemed like just a small step to assign the parameter value through
code. Does it just not work that way?

 

[Duane Hookom]

I never set parameters in code. I try not to set any criteria in the record
sources of forms and reports. If at all possible I use the Where Condition of
the DoCmd.OpenWhatever.

About the only time I don't do this is when the field isn't in the object's
field list or when the Record Source is for a subreport.

 

[dgunning]

Duane,

Thanks for responding, I tried using the where condition of DoCmd.OpenReport
again and am still having problems getting it to work. Here is the SQL for
the query that I want to report on:

SELECT DISTINCT ALLVAL.CLMNO, qrySumByClaim.Amttot
FROM ((ALLVAL INNER JOIN INVVAL ON ALLVAL.CLMNO = INVVAL.CLMNO) INNER JOIN
qrySumByClaim ON ALLVAL.CLMNO = qrySumByClaim.CLMNO) INNER JOIN
qryMaxAcctmoByClaim ON ALLVAL.CLMNO = qryMaxAcctmoByClaim.CLMNO
WHERE (((qryMaxAcctmoByClaim.MaxOfACCTMO)<[EOMDate]) AND ((INVVAL.INV) Is
Null Or (INVVAL.INV)=0) AND ((Abs([amttot]-[inv]))<1000))
ORDER BY ALLVAL.CLMNO;

Here's the SQL for the qrySumByClaim referenced above:

SELECT ALLVAL.CLMNO, Sum(ALLVAL.AMOUNT) AS Amttot
FROM ALLVAL LEFT JOIN INVVAL ON ALLVAL.CLMNO = INVVAL.CLMNO
GROUP BY ALLVAL.CLMNO;

and here's the SQL for qryMaxAcctmoByClaim:

SELECT Max(ALLVAL.ACCTMO) AS MaxOfACCTMO, ALLVAL.CLMNO
FROM ALLVAL
GROUP BY ALLVAL.CLMNO;

So in docmd.openReport my where condition is:

"([INVVAL].[INV] Is Null Or [INVVAL].[INV]=0) AND " & _
"(Abs([qrySumByClaim].[Amttot] - [INVVAL].[INV])<1000) AND
([qryMaxAcctmoByClaim].[MaxOfACCTMO]< " & rst("eomdate") & ")"

This gives me the error:

"The specified field [INVVAL].[VAL] could refer to more than one table
listed in the FROM clause of your SQL statement. (Error 3079)"

which puzzles me since I am explicitly declaring the table that the [INV]
field comes from - it's only in one table! I'm not sure where to go from
here, would appreciate any help.

 

[Duane Hookom]

In order to use the Where Condition, your report's record source would need
all of the [INV], [Amttot], and [MaxOfACCTMO] in the SELECT part of the SQL.
I'm not sure if this would work for you since your SQL includes DISTINCT. You
may end up with more records if you add the fields. If you add the fields to
your report's record source, you should remove the table qualifiers so your
Where Condition would be:

"Nz([INV],0)=0 AND " & _
"Abs([Amttot] - [INV])<1000 AND " & _
"[MaxOfACCTMO]< #" & rst("eomdate") & "#"

This assumes eomdate is a date field.

--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks for responding, I tried using the where condition of DoCmd.OpenReport
again and am still having problems getting it to work. Here is the SQL for
the query that I want to report on:

SELECT DISTINCT ALLVAL.CLMNO, qrySumByClaim.Amttot
FROM ((ALLVAL INNER JOIN INVVAL ON ALLVAL.CLMNO = INVVAL.CLMNO) INNER JOIN
qrySumByClaim ON ALLVAL.CLMNO = qrySumByClaim.CLMNO) INNER JOIN
qryMaxAcctmoByClaim ON ALLVAL.CLMNO = qryMaxAcctmoByClaim.CLMNO
WHERE (((qryMaxAcctmoByClaim.MaxOfACCTMO)<[EOMDate]) AND ((INVVAL.INV) Is
Null Or (INVVAL.INV)=0) AND ((Abs([amttot]-[inv]))<1000))
ORDER BY ALLVAL.CLMNO;

Here's the SQL for the qrySumByClaim referenced above:

SELECT ALLVAL.CLMNO, Sum(ALLVAL.AMOUNT) AS Amttot
FROM ALLVAL LEFT JOIN INVVAL ON ALLVAL.CLMNO = INVVAL.CLMNO
GROUP BY ALLVAL.CLMNO;

and here's the SQL for qryMaxAcctmoByClaim:

SELECT Max(ALLVAL.ACCTMO) AS MaxOfACCTMO, ALLVAL.CLMNO
FROM ALLVAL
GROUP BY ALLVAL.CLMNO;

So in docmd.openReport my where condition is:

"([INVVAL].[INV] Is Null Or [INVVAL].[INV]=0) AND " & _
"(Abs([qrySumByClaim].[Amttot] - [INVVAL].[INV])<1000) AND
([qryMaxAcctmoByClaim].[MaxOfACCTMO]< " & rst("eomdate") & ")"

This gives me the error:

"The specified field [INVVAL].[VAL] could refer to more than one table
listed in the FROM clause of your SQL statement. (Error 3079)"

which puzzles me since I am explicitly declaring the table that the [INV]
field comes from - it's only in one table! I'm not sure where to go from
here, would appreciate any help.

I never set parameters in code. I try not to set any criteria in the record
sources of forms and reports. If at all possible I use the Where Condition of
the DoCmd.OpenWhatever.

About the only time I don't do this is when the field isn't in the object's
field list or when the Record Source is for a subreport.