list box not allowing multiple selections

[jeffshex]

I have a report that prompts a dialog box when opened. This dialog has a list box and a date to and date from field. If I have the list box properties set to Not allow mulitple selections, then it works. But the moment I switch it to allow for multiple selections (which is what i need) it flat out doesn't return anything on the report, be it you choose one item or 10.
Any ideas on this one?

 

[Trevil]

Please post the recordsource and filter for the report.

Code: Where the vision is often rudely introduced to reality!

 

[jeffshex]

The record source for the report points to my query.
The criteria looks like this for the date field:
Between [Forms]![Reports by Team dialog]![StartDate] And [Forms]![Reports by Team dialog]![EndDate]
and like this for the subDepartment:
In ([Forms]![Reports by Team dialog]![cboTeam])
--even though that says cboTeam it is a listbox.

Here it is in SQL as well:
SELECT [Project Status].Date, [Project Status].Name, [Project Status].SubDepartment, [Project Status].Location, [Project Status].ProjectNumber, [Project Status].SystemProjectJob, [Project Status].Goals, [Project Status].ProjectManager, [Project Status].UpdateDescription, [Project Status].SickHours, [Project Status].VacationHours, [Project Status].HolidayHours, [Project Status].ProjectHours, [Project Status].TechHours, [Project Status].AdminHours, [Project Status].AfterHoursMF, [Project Status].AfterHoursSat, [Project Status].AfterHoursSun, [Project Status].SubTotalRegHours, [Project Status].SubTotalAfterHours, [Project Status].SubTotalLeaveHours
FROM [Project Status]
WHERE ((([Project Status].Date) Between [Forms]![Reports by Team dialog]![StartDate] And [Forms]![Reports by Team dialog]![EndDate]) AND (([Project Status].SubDepartment) In ([Forms]![Reports by Team dialog]![cboTeam])));

The only filters should be a date range and by sub department. I just need the user to be able to select any combination of sub departments for the report.

 

[Trevil]

The problem appears to be that if the multi-select property is set to anything other than 'None', the list box value is set to 'Null'. Someone else may provide a simple solution, but in case they don't, you could spin through the selected items and build a string that is placed into a hidden text box. Then change the query to say "... In (..HiddenBox...'



Code: Where the vision is often rudely introduced to reality!

 

[jeffshex]

I can't seem to find any other way around this. Would you mind giving me some more detail on the other way you proposed? It sounds like you are saying that whatever items are selected in the list box are added to a string delimited by a comma in a text box.
Am I correct?
Thanks!

 

[Trevil]

The following will build the string (enclosed in quotes):

Dim i As Integer
Dim sSearch As String

sSearch = ""
If Me.cboTeam.ItemsSelected.Count > 0 Then
For i = 0 To Me.cboTeam.ItemsSelected.Count - 1
sSearch = sSearch & Chr(34) & Me.cboTeam.ItemData(Me.cboTeam.ItemsSelected(i)) & Chr(34) & ","
Next i
Me.txtHidden = sSearch
Else
Me.txtHidden = "UserDidNotSelectAnything
End If


Good Luck! Wayne

Code: Where the vision is often rudely introduced to reality!

 

[jeffshex]

Ok, I understand it...you will have to forgive me, but I'm not 100% sure where it exactly goes. If it goes on the Form side or the Report side.
Is this an on cmdOK_Click event?
I was messin around with it for a bit, but nothin.

Thanks again!

 

[Trevil]

I believe you said the report has a dialog box, so I assume you must click 'OK' or something to close that box and run the report. If so, this code would go into that event. If the report calls the dialog box, then you may need to requery the report recordset after the selections are made.

Code: Where the vision is often rudely introduced to reality!

 

[dhookom]

This faq703-3936 might help some.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]