Open Report - pass filter params

[tweetyksc]

I'm going back to an "old" database that was designed a while ago and trying to streamline it.

There are 3 inventory reports that include the dept code for each inventory item [respdept].

There are 4 queries (for each report!!) that I have done to accomplish different filtering methods. I would like to streamline and have one query for each report and pass the filter through the OpenReport method which is run by a button on the form.

For instance:
all - prints all items - parameter blank
Casino - [respdept] between "1" and "200"
Tribal - [respdept] >199
Specific - (respdept would be chosen from a listbox)

I want the user to be able to choose "All", "Tribal", or "casino" and pass the applicable filter parameters.

I've tried doing it and am not having luck.
Here's the code that chooses the different report where I've added the condition text:

------------------------------------
Select Case optInvRange
Case 1
stDocName = strRptName & "ALL"
stCondition = ""
Case 2
stDocName = strRptName & "CASINO"
stCondition = "Between '1' and '200'"
Case 3
stDocName = strRptName & "TRIBAL"
stCondition = ">199"
Case 4
stDocName = strRptName & "SPECIFIC"
cboGC.SetFocus
If cboGC.Text = "" Then
MsgBox "Please Select a group code or another range", vbExclamation, "No Group Code Selected!"
Exit Sub
Else
stCondition = cboGC.Text
End If
--------------------------------------------
And here's where I've attempted to pull the parameters:

DoCmd.OpenReport stDocName, acPreview, [RespDept] = stCondition
--------------------------------

Can someone point me in the right direction?

 

[dcx693]

First, the DoCmd.OpenReport command WHERE parameter needs to look like this:

"[RespDept]=" & stCondition

Second, the condition for the casino numbers that you have:

stCondition = "Between '1' and '200'"
Are the casino numbers text values or numbers? If they are numbers, you don't need to delineate the values using the ' character. You can just use this:

stCondition = "Between 1 And 200"

Start off with those changes and see if you get any further.

 

[tweetyksc]

Thanks for replying!
I've tried fixing it a variety of different ways.
I'm currently getting the following error:
RTE # 3075
Extra ) is query expression '([RespDept]=)'

Here's the complete button vba.
I probably should have left "well enough" alone but how am I going to learn to streamline things if I do?
FYI - there are also 4 different REPORT VERSIONS of the 3 reports.
So you can see this needs a lot of streamlining.

-----------------------------------------------------
Private Sub cmdInvRpt_Click()
Dim strRptName As String
Dim stDocName As String
Dim stCondition As String


Select Case optInvName
Case 1
strRptName = "GroupCodeInv"

Case 2
strRptName = "InvMgmt"

Case 3
strRptName = "WHStorage"
End Select

Select Case optInvRange
Case 1
stDocName = strRptName & "ALL"
stCondition = ""
Case 2
stDocName = strRptName & "CASINO"
stCondition = "Between 1 and 200"
Case 3
stDocName = strRptName & "TRIBAL"
stCondition = ">199"
Case 4
stDocName = strRptName & "SPECIFIC"
cboGC.SetFocus
If cboGC.Text = "" Then
MsgBox "Please Select a group code or another range", vbExclamation, "No Group Code Selected!"
Exit Sub
Else
stCondition = cboGC.Text
End If

End Select

Select Case optInvFormat
Case 1
DoCmd.OpenReport stDocName, acViewNormal, , "[RespDept]=" & stCondition

Case 2
DoCmd.OpenReport stDocName, acPreview, , "[RespDept]=" & stCondition

Case 3
DoCmd.SendObject acSendReport, stDocName, "SnapShot Format(*.snp)", , , , "Inventory Report", _
"If you cannot open this file, install the free snapshot viewer from http://www.microsoft.com/AccessDev/ProdInfo/snapshot.htm"
End Select

End Sub
-------------------------------
Of course, I haven't touched on how to do the last case #3 yet, sending through email.

 

[tweetyksc]

Just wanted to post that I've got this working - finally!
In case this can help someone else.

Had to take a closer look; the "=" was bothering me, because it really didn't fit.

Here's what worked, taking the "=" out of the docmd line, as the ">" and "between" conditions took the place, then adding it to the line where the user picks the value as it is the only place it applies:


Instead of:
--------------------------------------------------------
DoCmd.OpenReport stDocName, acViewNormal, , "[RespDept]=" & stCondition
------------------------------------------------------------------

Replaced with:
------------------------------------------------------------------------
DoCmd.OpenReport stDocName, acPreview, , "[RespDept]" & stCondition
----------------------------------------------------------------------


Then to allow for the option where the user chooses the respdept from the form:

Instead of:
----------------------------------------------------------
stCondition = cboGC.Text
----------------------------------------------------------

Replaced with:
----------------------------------------------------------
stCondition = "like " & cboGC.Value
----------------------------------------------------------

OK, now I have to figure out how to deal with the last option; if a user wants to send the report...maybe I have to do that in a separate sub? As how do I filter it and send it with sendobject?

I would also like to pare down the # of reports by inserting info into the headers/footers from code, instead of separate ones, but I've tried to find the answer to that before without success.