multiple selections in a list box as criteria for a report?

[alexi]

hello,
i have a form in which i select a job no in a list box, that then showes the relevant tasks for that job in another list box in which multiple select is allowed. i then put the folliwing code behind the view report button

Private Sub cmdPrint_Click()
Dim strWhere As String

strWhere = "[JobNumberFieldName] = " & Me!lstJobNumberListBox
strWhere = strWhere & " AND [TaskNumberFieldName] In "
strWhere = strWhere & fustrCriteriaFromListbox(Me!lstTheTaskListBoxControlName

DoCmd.OpenReport "ReportName",,,strWhere

End Sub

but when i select everything i need to on the form and press the button i get the following error:

In Operator Without () In query expression '([JobNo] = 172/03 AND [TaskNo] In)'

Any Ideas what im doing wrong?

Thanks

 

[alexi]

sorry btter make a change to that the code i wrote in vb is wrong here is the correct code


Private Sub GenerateReport_Click()
Dim strWhere As String

strWhere = "[JobNo] = " & Me!JobNo
strWhere = strWhere & " AND [TaskNo] In "
strWhere = strWhere & Me!TaskNo

DoCmd.OpenReport "qryInvoice", , , strWhere

End Sub

 

[Pat Hartman]

JobNo looks to me like a text field so you'll need to surround it with quotes. strWhere = "[JobNo] = '" & Me!JobNo & "'" You probably also have missing parentheses unless they're embedded in the Me!TaskNo string.

Put a stop in your code so you can print out the contents of strWhere as you build it. It needs to look like a valid Where clause when you're done.


[JobNo] = '172/03' AND [TaskNo] In (1,2,3)

 

[alexi]

what do u mean by missing paranthesis. and embedding?
this code even with the inverted commas still gives the same msg. even though the [JobNo] is now in inverted commas

this is really bugging me

 

[KevinM]

If you want to use all the selected values in a mutlti-selct list box then you will need to use the ItemsSelected collection within a loop of code.

I use this to build a query and then base the rpt on that query linked to another to restrict the report to only show the selected values.

You can't simply reference a ListBox like...

Me!lstJobNumberListBox

This will only work with single select list boxes.

 

[nightwalker]

Similar Problem

I have a similar problem:
I have a form which has the fields of the Table as checkboxes and i want to make a query that will show only selected fields in the reports.

That is, if somebody choose "ABC" and "DEF" will only see these two fields and if chose "ABC" and "XYZ" will see these two.

There are more than 9 fields to take care of and report has to invoked on the click of a button.

Can anybody guide me in doin this