Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can I NOT pass two parameters to OpenReport Method?

Status
Not open for further replies.

mongous

Technical User
Jun 18, 2002
116
US
I am trying to pass two variables, but can't seem to get it to work. Code:
[red]
Private Sub cmdRepStatMtdRptPreview_Click()
cboTeams.SetFocus
DoCmd.OpenReport "rptRepStats_MTDReport", acViewPreview, , _
"[Team] = Forms![frmChooseReport]![cboTeams].Text" And _
"[Month] = Forms![frmChooseReport]![cboChooseMonth].Text"

End Sub
[/red]

Am I missing something? With this code I get 'Type Mismatch' Error...
 

Should be:
DoCmd.OpenReport "rptRepStats_MTDReport", acViewPreview, ,
"[Team] ='"& Forms![frmChooseReport]![cboTeams].Text&"' And [Month] = '"&Forms![frmChooseReport]![cboChooseMonth].Text &"'"

Don't use ' if the field is numeric

 
hhmmm...
with that i get a Compile Error: Expected: End of Statement

It highlights the [red]"'[/red]
 
Mongous, you may need to double check the single and double quotes. The have to be in a specific order.

Code:
 "[Team] ='" & Forms![frmChooseReport]![cboTeams] & "' And [Month] = '" & Forms![frmChooseReport]![cboChooseMonth] & "'"


Paul

 
Cut and paste this:

DoCmd.OpenReport "rptRepStats_MTDReport", acViewPreview, , "[Team] ='" & Forms![frmChooseReport]![cboTeams].Text & "' And [Month] = '" & Forms![frmChooseReport]![cboChooseMonth].Text & "'"
 
Paul: I have tried to play with the quotes a little, to no avail, and vbajock: I cut an pasted and ran, now I simply get: Syntax Error...
 
Try changing the syntax to this.

Code:
DoCmd.OpenReport "rptRepStats_MTDReport", acViewPreview, , "[Team] ='" & Forms![frmChooseReport]![cboTeams] & "' And [Month] = #" & Forms![frmChooseReport]![cboChooseMonth] & "#"

if Month is a Date value
or this

Code:
DoCmd.OpenReport "rptRepStats_MTDReport", acViewPreview, , "[Team] ='" & Forms![frmChooseReport]![cboTeams] & "' And [Month] = " & Forms![frmChooseReport]![cboChooseMonth] & ""

if Month is a numeric value.

Paul
 
if your using combo boxes the property is .Value, not .text
 
You shouldn't need .Value or .Text for the expressions to work. I took them out in my posts.

Paul
 
Yes, I would have to agree that knowing the difference is important.

Paul
 
Now it (the report) starts to run but I get input boxes for 'Start Date' and 'Month' ???

It's supposed to get the month value from the combo box. I am not sure where it got 'Start Date'.

Paul: Thsi might have something to do with my 'month' cboBox. You helped me display it correctly yesterday...

vbajock: Thanks for the correction, I wasn't sure if it was value or text...
 
I sort of assumed it did. The format is
January 2003
so that might make a difference. Also, I don't know where the Start Date comes from.
I'm sorry, I'm leaving and will check in later but I'll be gone for a bit.

Paul
 
I wonder if instead of all this it would have been easier to just write a routine that would automatically pick all records within that month. i.e. 'January 2003' would pull 1/1/03 - 1/31/03 etc...
 
If the form is open at the same time you print the report, the easy way to do it is to create a query as the record source of your report and put Forms![frmChooseReport]!cboTeams] as the criteria in the Teams column and
Forms![frmChooseReport]![cboChooseMonth] as the criteria in the Month column. Then just call the Openreport method without a where clause.

If you are getting parameter boxes popping up when you open your report, you have fields imbedded in your report that are unbound. Re-check your report and see if you have any of these fields either in the report, or in the query it is based on, or in the Sorting and Grouping box. Give them a data source or remove them.

 
Cool, Renewed hope! Thanks. I am off for the day but will check it out...


Thanks again!
 
vbajock,

That's an easy method, but when you want to use that query for anything else you can't because of the "embeded" criteria. Passing the where condition allows the query to be filterd many ways.

I just say that because I used to use the easy method a lot, but after a while with many queries you start to get bogged down. And start creating queries for one report, when you could make one for several reports. And one other very important thing.

The more important reason not to use it is this:
Scenario: After the user opens the report in preview. They close the form, but leave the report open to review it before printing. When decide to print they get a input box requesting the criteria with a message they don't understand like: [forms]![frmCustomers]![CustomerID]. That won't happen when passing the where string.

It's subtle, but it happens.

Hope that hells.

Mark P.

Bleh
 
Just another viewpoint...
What I do with reports that get hairy with a lot of different parameters and filters and WHERE clauses coming from all over the place is the following:

Create 2 queries, named with the convention:
qrySomeReport_Base --this has SELECT only--no WHERE or ORDER BY
qrySomeReport --This is the report's Source; sql gets dynamically set so it can start as dummy select only

In the code where you open the report:
(assume all Dim's)

set qd = db.Querydefs("qrySomeReport_Base") 'Select ONLY
strSQ = qd.Sql
'pick your method to Replace() or Left$() out the trailing ";"

strWhr = (do your stuff to gather the WHERE's from all over creation)

set qd = db.Querydefs("qrySomeReport")
qd.sql = strSQ & " WHERE " & strWhr

docmd.openreport "rptSomeReport" 'now the soruce query is coded with all the where clauses you need

A tiny (really it's not that much work) bit of preliminary work, but it's so much more flexible down the road.

If your 'base' query is a Group By, you need to parse for where to insert the WHERE clause, but that's not a big deal. First make sure you actually need a Group By as the base--I see many people use Aggregate queries as a report's base in cases where they may not be needed because the report itself will do that..same with putting Order BY in the base query.
--jsteph
 
To me, its important to introduce a person that's new to Access to all the techniques available. The choice on which technique is better is usually unique to the application or dependent on the skill level of the programmer.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top