using string variables with OpenReport

[PatSalmon]

I have the following code which is supposed to take the selection from a list box and run a report based on that selection. I am have trouble getting the quotation marks right (I think that's my problem). I have tried many different formats. This current one does not give me an error message, but it also gives me a blank report, even though the data is there.

Any help would be appreciated

Thanks
Pat

Private Sub Command2_Click()
On Error GoTo ReportErr
Dim strWhere As String
If ListNames = 0 Then
strWhere = ""
Else
strWhere = "[Lookup] = """ & [ListNames] & """"
End If
DoCmd.OpenReport "rptByLNameSloc", acViewPreview, , strWhere
Exit Sub
ReportErr:
Select Case Err.Number
Case 2501
Exit Sub
Case Else
Dim strMessage As String, strTitle As String
strMessage = "Error " & Err.Number & ", " & Err.Description & "has occurred."
strTitle = "Unexpected Error"
MsgBox strMessage, vbInformation, strTitle
End Select
End Sub

 

[JohnLowell]

What is ListNames? If it is a single name then check if
Len(ListNames) < 1

Also, change the strWhere statement to be this:

strWhere = &quot;[Lookup] = &quot; & chr(34) & [ListNames] & chr(34)

 

[PatSalmon]

ListNames is the list box control name. The names listed are in the format Salmon P. - I used to have a comma after the last name but thought that it might be causing me problems so I removed it. I have added an &quot;All&quot; selection which the

If ListNames = 0 Then
strWhere = &quot;&quot;

is check for so that the report runs for all names, this part works.


Where should I place Len(ListNames) < 1

I made the change to the strWhere statement, the results were the same. No data in the report.


Thanks for the reply, hopefully this will clarify my problem at bit.

Sincerely,
Pat

 

[JohnLowell]

You should put a breakpoint in the code on the If ListNames = 0 Then statement.

Select a name from the box and try to print it. I'm curious to see what the value of ListNames is.

 

[PatSalmon]

When I select &quot;All&quot; this is the watch I created

Watch : + : ListNames : &quot;0&quot; : Object/ListBox : Form_frmSelectionBuild.Command2_Click

When I select an idividual name I get

Watch : + : ListNames : &quot;Blair T.&quot; : Object/ListBox : Form_frmSelectionBuild.Command2_Click

Does this make sense?

Thanks
Pat

 

[JohnLowell]

I can't see anything wrong.

Do you want to send me the form, report and tables to take a look at it?

 

[PatSalmon]

Thanks John,

Let me know your email address and I will send you what I have.

thanks again
Pat

 

[JohnLowell]

Send it to jmcanespie@attbi.com

 

[JohnLowell]

Go to the query qryNameSelectionBuilder and remove the extra spacein the middle. It should be & &quot; &quot; & (1 single space)

 

[PatSalmon]

WOW!!! You're awsome. I knew it must be something simple, but that is so hard to spot.

Thanks a million!!!

Pat