Syntax for passing two parms to a report?

  • Thread starter Thread starter Dean Slindee
  • Start date Start date
D

Dean Slindee

A stored procedure that is the source for an Access report requires two
parameters. However, the default syntax seems to allow for only one
parameter. Hope that I am wrong. Here is what I have tried thus far:

DoCmd.OpenReport "rptJuvenileParticipantForm", acViewNormal, ,
[Forms!frmJuvenileProgram2.ClientID, Forms!JuvenileProgram2.ProviderID]
DoCmd.OpenReport "rptJuvenileParticipantForm", acViewNormal, , ClientID,
ProviderID

I have checked that the values contained in the above statement are correct,
but the statement is not of a correct syntax.

Thanks for any hints,

Dean Slindee
 
Are you talking about a ADP project here...

Are those parameters being sent to a stored procedure on sql server?

Or, are you talking about a normal report that is based on a standard
ms-access query?

In general, most flexible approach is to get rid of the parameters in the
sql, and then simply use the "where" clause of the open reports.

Using the where clause have MANY advantages,

You don't mess up your sql and have a bunch of weird parameters in it.

You can use the sql for more then one report..since again..all that is in
the query is nice clean sql..and no parameters.

You don't have to define the number of parameters BEFORE hand,and thus again
you can re-use the sql for more reports.

You can build reports that don't need all of the parameters!

(this list could go on for quite a bit......lets just stop..and get one with
the solution).

Simply just base the report on some nice clean sql with no parameters at
all.

Then, just go:

dim strWhere as string

strWhere = "(ClietnID = " & me!ClientID & ")" & _
" and (ProviderID = " & me!providerID & ")"

Of couse, if the code you are running is NOT in the
forms!frmJunvenileProgram2, then you have to add the forms qualification to
the above...

You would get:
strWhere = "(ClietnID = " & forms!frmJunvenileProgram2!ClientID & ")" & _
" and (ProviderID = " & forms!frmJunvenileProgram2!providerID &
")"

Now, after picking which one of the above is appropriate , the next line of
code is:

DoCmd.OpenReport "rptJuvenileParticipantForm", acViewPreview,,strWhere

So, that "where" clause can be as complex as you like.and you don't have a
particular limit on the parameters (this again is a great reason why to use
the
where clause..as you get a LOT of flexibly here...).
 
ADP Project against a SQL Server backend with all communication being done
thru stored procedures.

I really like your overall Where clause approach and will use it in the
future where possible, thanks.

However in this case, the stored procedure is joining two tables based on
the first parameter, ClientID. It then limits the rows from the second
table by the ProviderID. Since the join depends on one of the parameters,
it looks like the elegant Where clause approach might not work?

I suspect that the DoCmd.OpenReport statement syntax allows for only one
input parameter. Hoping that is not true....

Dean Slindee
 
I suspect that the DoCmd.OpenReport statement syntax allows for only one
input parameter. Hoping that is not true....
Click to expand...

No, the where clause is any valid legal sql "where" condition (you just
leave out the word "where").

You can have 5, or 15 conditions. You are supplying a valid sql "where"
clause here.

I would create a view of the join..and use that for the rpoert .and then try
the where clause idea.

This save all kinds of having to write parameters etc.
 
Back
Top