Passing multi-variable string to report (1 Viewer)

[kodehunt]

Below is the code I am using to pass a multi-variable string to a report. When I run the code I get the message in the "Parameter.jpg" attachment. I added "msgbox.jpg" attachment to show what the string value is. The NameStr is passing fine, but the dateStr is not. Both are text fields in the database. If I type "2012 Spring" (with no quotes) in the Parameter box the variable passes fine and report pops up filtered correctly. Not sure what to change, any help is much appreciated.

Private Sub bSubmit_Click()
Dim NameStr As String, dateStr As String
Dim SQLStr As String
Dim ListCounter As Integer
Dim AnItem As Variant
Dim strReport As String

strReport = "rIndividualReport"

For Each AnItem In Me.lName.ItemsSelected
If Not IsNull(AnItem) Then
NameStr = NameStr & "'" & Me.lName.ItemData(AnItem) & "',"
End If
Next AnItem

If Len(NameStr) = 0 Then
MsgBox "You did not select a Customer" _
, vbExclamation, "Nothing to find!"
Exit Sub
Else
NameStr = Left(NameStr, Len(NameStr) - 1)
End If

For Each AnItem In Me.lDate.ItemsSelected
If Not IsNull(AnItem) Then
dateStr = dateStr & "'" & Me.lDate.ItemData(AnItem) & "',"
End If
Next AnItem

If Len(dateStr) = 0 Then
MsgBox "You did not select an IOR Date" _
, vbExclamation, "Nothing to find!"
Exit Sub
Else
dateStr = Left(dateStr, Len(dateStr) - 1)
End If

SQLStr = "SELECT tIORData.[Name], tIORData.[Review Date] " & _
"FROM tIORData " & _
"WHERE tIORData.[Name] IN(" & NameStr & ") and tIORData.[Review Date] IN(" & dateStr & ")"
MsgBox SQLStr
DoCmd.OpenReport strReport, acPreview, filtername:=SQLStr
DoCmd.Close acForm, "fLoadCustomerReport"
End Sub

 

[pbaldy]

The parameter prompt is telling you it can't find that field, so first thing is to double check the spelling.

 

[kodehunt]

Checked that and it looks fine. Also if I just type "Spring 2012" (without the quotes, which is the only date available to select at this time) in to the parameter prompt, the report runs fine and filters correctly.

 

[pbaldy]

Can you post the db here?

 

[kodehunt]

Here it is.

 

[pbaldy]

How do I get the error? I see similar code in fLoadCustomerForm which runs without error.

 

[kodehunt]

The error only happens when you run the report, not the form. That's part of the weirdness, it's essentially the same code, works for the form but doesn't work for the report.

 

[kodehunt]

Any additional thoughts?

 

[pbaldy]

I was looking for the actual steps required. As originally mentioned, the review date field is not in the record source of the report. When I add it, the report opens without the prompt.

 

[GregRun]

You have an entire SQL statement in the filter.

You just want [xField] = "xValue" AND [yField] = "yValue"

 

[pbaldy]

You have an entire SQL statement in the filter.

You just want [xField] = "xValue" AND [yField] = "yValue"

Filter <> wherecondition

Personally I always use the wherecondition argument, but the OP is using the filter argument, which has different requirements. I thought it had to be a saved query, but I've never actually used it.

 

[GregRun]

Oops! Sorry about that. I haven't ever used the filtername property and Access Help isn't all that helpful about how to use it.

 

[GregRun]

http://msdn.microsoft.com/en-us/library/bb243764(v=office.12).aspx

 

[kodehunt]

I was looking for the actual steps required. As originally mentioned, the review date field is not in the record source of the report. When I add it, the report opens without the prompt.

I misunderstood your first post, that was exactly it, thank you very much.

 

[pbaldy]

Happy to help, and welcome to the site by the way!