Using variables with the docmd.openreport

[dpedley]

I'm trying to write code so that a user can open a form and pick a report from one list and a city from another. The form is working fine. But my problem is trying to get the right syntax to use the docmd.openreport. It should be simple

DoCmd.openreport test, acViewPreview, , "office_worked =" & testLoc

'test' being the variable holding the report name, and 'testloc' the Location.

This returns an empty set. When I tried simply replacing the variable with hard code. I got this to work.

DoCmd.openreport test, acViewPreview, ," [office_worked]= ""ATL"" "

ATL of course is the code for Atlanta. Works great. I have tried countless ways to substitute ""ATL"" with the 'testloc' variable, to no avail. Finally I created a small proc (below) which used only a dimmed value 'crit' to see what was coming out in my combination, and the Docmd statement. In several previous attempts I've gotten the 'crit' to come out looking identical to the statement above which works, but I still get an empty set. The code below is back to the beginning.

Private Sub Test_DblClick(Cancel As Integer)

Dim crit As String

crit = " [office_worked] =" & testLoc
DoCmd.OpenReport test, acViewPreview, , crit

End Sub

 

[MajP]

"office_worked = '" & testLoc & "'"