We help IT Professionals succeed at work.
Get Started

VBA to select Year from a multi-select list box is not working

fabi2004 asked
Last Modified: 2017-04-06
I have a split form with several multi-select list boxes in the header used to filter the results in the datasheet.  I build a filter with results of all the list boxes and then apply it to the datasheet.

I have a problem with the first list box selections.  It's a year list.  If I leave it blank, the rest of the code works fine.  But if I choose one or more items from the year list, they are ignored.

I believe it has to do with the way I'm trying to capture the first criteria (if no selection has been made in the year list box). Because if I comment that part out, it works fine except for throwing an error that there's an extraneous "And" in my criteria.

Can someone take a peek and see if it's something obvious I'm coding wrong please?

Sub Search()
Dim strSearch, strCriteria, strYear, strMonth, strStatus, strState, strAM, strREC, strBD As String
Dim task As String
Dim varItem As Variant

'''===========code for Year List Box

If IsNull(Me.lstYear) Or Me.lstYear = "" Then
    strYear = "([DateYear] like ""*"")"
    strCriteria = strYear
   For Each varItem In Me!lstYear.ItemsSelected
    strYear = strYear & "[DateYear] = " & Me!lstYear.ItemData(varItem) & "Or"
Next varItem
    If Len(strYear) > 0 Then
        strYear = Left(strYear, Len(strYear) - 2)
        strCriteria = strCriteria & " And (" & strYear & ")"
    End If

Open in new window

After building the remaining criteria

task = "select * from [qryFinancialsByMonth2] where (" & strCriteria & ")"
Me.FilterOn = True
DoCmd.ApplyFilter task

Open in new window

Watch Question
Top Expert 2016
This problem has been solved!
Unlock 1 Answer and 30 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE