Openreport Method - 'Where' Argument Help

[Elana]

I am having fits trying to concatenate a variable into my Where argument. I've tried everything to fix it, but no luck.

I have a report that will print whatever record is selected in my listbox located on form 'frmPrintMenu.' The bound column of the listbox is not the criteria for this particular report, however. The criteria is located in column 5 of the listbox. So, I have a 'Print' button that specifies (in part):

Dim strWhere as string
Dim strPropEndtNo As String

strPropEndtNo = Me.lstSelectEndt.Column(5)

strWhere = "[ChangeNoProp] = " & strPropEndtNo

DoCmd.OpenReport stDocName1, acPreview, , strWhere

This code results in the error "data type mismatch in criteria expression." So I tried to enclose strPropEndtNo in quotes...this then opens up a Parameter dialog box when attempting to run the code.

As an experiment, I placed a text Box called txtPropEndt and passed the value of column 5 into that box. When I make my strWhere = "[ChangeNoProp] = " & "Forms!frmPrintMenu!txtPropEndt", it runs fine and selects the correct record.

I would like to avoid the extra step of passing the value into a text box.

What on earth am I doing wrong?

 

[kidzmom3]

Why are you not basing your report directly on a query with the query parameters?

 

[Elana]

Because I have several reports to run from this code and I don't want to make a query for each one.

 

[Elana]

I figured it out. I wasn't enclosing the variable in quotes - here is one solution to the problem:

strWhere = "[ChangeNoProp] = """ & strPropEndtNo & """"

Works great -