By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,550 Members | 2,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,550 IT Pros & Developers. It's quick & easy.

Filter Report Using Subroutine Variable

P: 2
I am relatively new to using VBA and am have been trying for two days to pass a user entered value to my report for filtering when opened. I have the following subroutine in the open event of my report:
Private Sub Report_Open(Cancel As Integer)

'Prompts for business unit when run educational assistance reports.

Dim strBusUnitName As String

strBusUnitName = UCase(InputBox("Enter Business Unit or click OK for all.", "Business Unit", "All"))

Me.Filter = ""
Select Case strBusUnitName

Case Is = "DB17A", "DB50A", "DB69A", "DB70A", "DB71A", "DB85A"
Me.Filter = "[EmpBusinessUnit]= " & strBusUnitName
Me.FilterOn = True
Case Is = "ALL"
Me.FilterOn = False
Case Is = ""
Me.FilterOn = False
Cancel = True
Case Else
MsgBox "Business Unit Not Found. Please Verify and Retry."
Cancel = True
End Select
When my form opens I am prompted for the business unit. When a valid business unit is entered Access brings up a second prompt with the title: "Enter Parameter Value" with the value of my filter variable where the text prompt would go: (ex. DB69A would show as the text for the second prompt).

Can someone please tell me how to get rid of this second prompt and get access to use the value I am passing in variable strBusUnitName to filter the report.

Thank you.
Nov 17 '08 #1
Share this Question
Share on Google+
2 Replies

P: 2
I don't know if it is good protocol to answer your own post, but here is the solution for those of you who run into a similar problem:

The syntax for a string variable passed as a filter is as follows:
Me.Filter = "[EmpBusinessUnit] = '" & strBusUnitName & "'"
DQ = Double Quote sq = Single Quote as follows:

DQ[EmpBusinessUnit] = sqDQ & strBusUnitName & DQsqDQ

This is wild but it is correct.

Thanks to PSchiele at the following link for having posted this solution to a post from 1999 - Wow. {Tek-Tips - PSchiele Post 1999}
Nov 17 '08 #2

Expert Mod 15k+
P: 31,494
We're quite happy for you to post a solution Jim. Unfortunately, due to our rules, I have had to remove the link to a competing forum.

You can find a discussion of the topic here at Quotes (') and Double-Quotes (") - Where and When to use them.
Nov 17 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.