troubleshooting Question

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

Avatar of fabi2004
fabi2004Flag for United States of America asked on
VBAMicrosoft Access
30 Comments1 Solution183 ViewsLast Modified:
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
Else
   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

After building the remaining criteria

task = "select * from [qryFinancialsByMonth2] where (" & strCriteria & ")"
Me.FilterOn = True
DoCmd.ApplyFilter task
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 30 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 30 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros