Filter Report Using Subroutine Variable

[jim190]

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.

[jim190]

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}

[NeoPa]

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.