Multi Select List Box to filter Report 1

[ssecca26]

i spend whole week to solve this problem but until now i cant solve it, can someone help me please.

i have this in a form

lstArea ---- Area 1-4
lstCP - CP01 - 10
Name - 4 Names
cmbfrom - date from
cmbto - date to
sections - section in my table - fieldnames(Section 1- 5)
cmdpreview - preview a report


i want a report to filter my choices in the above criterias, if the box is empty means all


please help me, i really need to get this code.


Thanks!

 

[ssecca26]

but i want to select all if the user didnot tick any of the section.

please help me solve this problem.


thanks!

 

[FancyPrairie]

Sorry, I forgot about that part. Therefore, change this line of code within the For-Next loop:

If (frm!lstSection.Selected(i - 1)) Then

to this

If (frm!lstSection.Selected(i - 1)) Or (frm!lstSection.ItemsSelected.Count = 0) Then

 

[ssecca26]

so its gonna be like this?

Private Sub Report_Open(Cancel As Integer)

Dim strWhere As String
Dim strSQL As String
Dim strSection As String
Dim frm As Form
Dim i As Integer

Set frm = Forms!form1

strSection = ""

For i = 1 To 4 '4 represents the total number of sections
If (frm!lstSection.Selected(i - 1)) Or (frm!lstSection.ItemsSelected.Count = 0) Then strSection = strSection & "[Weekly Report].[" & frm!lstSection.ItemData(i - 1) & "], [Weekly Report].[" & frm!lstSection.ItemData(i - 1) & "_Details], "
Else
strSection = strSection & """N/A"" As [" & frm!lstSection.ItemData(i - 1) & "], ""N/A"" AS [" & frm!lstSection.ItemData(i - 1) & "_Details], "
End If

Next

strSQL = "SELECT [Weekly Report].ID, [Weekly Report].Project_Week, [Weekly Report].Area, [Weekly Report].CP, [Weekly Report].Name "
If (strSection <> "") Then
strSection = Mid(strSection, 1, Len(strSection) - 2)
strSQL = strSQL & ", " & strSection
End If

strSQL = strSQL & " FROM [Weekly Report] "
strWhere = BuildWhere(frm)

If (strWhere <> "") Then strSQL = strSQL & " WHERE " & strWhere

MsgBox strSQL
Me.RecordSource = strSQL

End Sub

 

[FancyPrairie]

That's correct

 

[ssecca26]

is not working, same problem then if statement is has error

 

[FancyPrairie]

The code works.

1. Note that in the last example I gave you, the name of the form that contains the listboxes, etc. is named form1.
Replace form1 with the name of your form.

2. Is the name of the listbox, that contains the Section choices, lstSection. That's what the code is expecting.

3. If that doesn't work, you will have to step thru it with debug (as I described in a previous post). Then report back with the error message and the line it errored on.

 

[ssecca26]

thats the only way to choice the fields in list?

thanks i'll try it again.