Can I NOT pass two parameters to OpenReport Method?

[mongous]

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...

 

[vbajock]

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

 

[mongous]

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

It highlights the [red]"'[/red]

 

[PaulBricker]

Mongous, you may need to double check the single and double quotes. The have to be in a specific order.

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

Paul

 

[vbajock]

Cut and paste this:

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

 

[mongous]

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...

 

[PaulBricker]

Try changing the syntax to this.

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

if Month is a Date value
or this

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

if Month is a numeric value.

Paul

 

[vbajock]

if your using combo boxes the property is .Value, not .text

 

[PaulBricker]

You shouldn't need .Value or .Text for the expressions to work. I took them out in my posts.

Paul

 

[vbajock]

Yeah, but its good to know that combo is weird like that

 

[PaulBricker]

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

Paul

 

[mongous]

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...

 

[PaulBricker]

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

 

[mongous]

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...

 

[vbajock]

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.

 

[mongous]

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


Thanks again!

 

[markphsd]

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

 

[jsteph]

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&quot '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&quot
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

 

[vbajock]

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.