VBA to select Year from a multi-select list box is not working
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 StringDim task As StringDim varItem As Variant'''===========code for Year List BoxIf IsNull(Me.lstYear) Or Me.lstYear = "" Then strYear = "([DateYear] like ""*"")" strCriteria = strYearElse 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
to check if you have the syntax correct
debug.print strCriteria
fabi2004
ASKER
Hi Rey, thank you. I definitely missed that. However, it didn't solve the problem. If I select 2016 for the year and Jan for the month, I should retrieve an empty recordset, but the datasheet shows records for Jan 2017.
I can't be sure, but I think that something with the initial criteria string is overriding the varItem selection.
If I comment the following out, it seems to work fine. With the exception that it doesn't like that leading "And".
If IsNull(Me.lstYear) Or Me.lstYear = "" Then strYear = "([DateYear] like ""*"")" strCriteria = strYear
Alight, that will take me a few minutes to re-combine the ME & BE. I'll upload a copy asap.
fabi2004
ASKER
Rey, I'm having to upload the two parts because when I tried to import everything into one acdb file I kept getting errors when running the queries/forms.
I cannot open your application right now (iPhone), but it seems to me, if you want to select potentially multiple years, and one or month, then you need to construct a query with syntax something like:
([DateYear] = 2017) And ([DateMonth] = 12)
or possibly:
([DateYear] IN (2016, 2017)) And ([DateMonth] = 12)
or possibly
([DateYear] IN (2016, 2017)) And ([DateMonth] IN (11, 12)
I generally do this something like:
Dim varItem as variantDim varYears as variant, varMonthsDim strCriteria as stringvarYears = NULLfor each varItem in Me.lstYear.ItemsSelected varYears = (varYears + ", ") & me.lstYear.itemdata(varItem)Nextif isnull(varYears) = false then if me.lstYear.ItemsSelected.Count = 1 Then strCriteria = "([DateYear] = " & varYears & ")" else strCriteria = "([DateYear] IN (" & varYears & "))" endifendifvarMonths = NULLfor each varItem in Me.lstMonth.ItemsSelected varMonths = (varMonths + ", ") & me.lstYear.itemdata(varItem)Nextif isnull(varMonths) = false then if strCriteria <> "" then strCriteria = strCriteria & " AND " if me.lstYear.ItemsSelected.Count = 1 Then strCriteria = strCriteria & "([DateMonth] = " & varMonths & ")" else strCriteria = strCriteria & "([DateMonth] IN (" & varMonths & "))" endifendif
Rey, that works beautifully. You stripped the 'And' from the beginning of the string if there was a selection made in the list box. I also noticed you changed * from text. Perfect. Thank you so much.
I'm about to run a debug compile for the first time on this db. It's still very much a "work in progress". I have a lot of things I need to delete out of it that I've moved on from. I tend to hold off on deleting until I'm absolutely certain I have a new working option.
Anyways, I'm assuming I'm going to run across a lot of errors on the debug. So, I'll be back as soon as I can.
fabi2004
ASKER
Hi Dale,
Thank you for responding. Wow, you wrote all that on your phone? I'm impressed.
I think I have code similar to what your posted. I have several multi-select boxes to be used as filters on this form.
strYear = "([DateYear] like '*')"
strCriteria = strYear
if there is none selected in lstYear, leaving the filter for the [DateYear] blank will return all years.
fabi2004
ASKER
OK, that makes sense. The reason for
strYear = "([DateYear] like '*')"
strCriteria = strYear
was to avoid that dangling "And" at the beginning of the string
I guess this means I need to look at all the rest of those list boxes and change those too?
I commented out that entire If at the beginning of the Year selection and just left the For loop. I tried running it choosing Jan and Dec from Months with the Year selection blank but it gives me this error
Run-time error '3075'
Syntax error (missing operator) in query expression '(And ([DateMonth] = 1 OR [DateMonth] =12))'.
Which I think means I need to go ahead and modify the rest of the code for each of the additional list boxes. Does that sound right to you?
Rey Obrero (Capricorn1)
yes you have to modify your codes.
in the codes for the months, check if years were selected with
'''=========== code for Month List box
For Each varItem In Me!lstMonth.ItemsSelected
''' Use "Or" to get result from all Month
strMonth = strMonth & "[DateMonth] = " & Me!lstMonth.ItemData(varItem) & " Or "
Next varItem
If Len(strMonth) > 0 Then
strMonth = Left(strMonth, Len(strMonth) - 4)
If strCriteria & "" = "" Then '<<<<<<<<<<<< this means that there were no years selected
strCriteria = "(" & strMonth & ")"
Else
strCriteria = strCriteria & " And (" & strMonth & ")"
End If
End If
fabi2004
ASKER
Is this way going to cause a problem if a user selects from multiple list boxes in random order (not from left to right or first to last?)
if that is your worry, use the test for strCriteria for all the listboxes, but it is actually not needed for lstYear since it is first on the list that will be evaluated when you run the codes for search
fabi2004
ASKER
Add If strCriteria & "" = "" Then ' to all the list boxes?
The next list boxes are all text, I seem to be missing a closing set of quotes because the Immediate window says: ([StatusCode] = "C)
After this one I think I can pattern the rest of them on this one and be good with this search filter.
This is the new Status List box code. Do I need another Chr(34)?
''''=========== code for Status List boxFor Each varItem In Me!lstStatus.ItemsSelected' use ASCII Chr(34) to replace Double Quotes in VB'Chr(34) = "' Use "Or" to get result from all Statuss strStatus = strStatus & "[StatusCode] = " & Chr(34) & Me!lstStatus.ItemData(varItem) & Chr(34) & " Or "Next varItem If Len(strStatus) > 0 Then strStatus = Left(strStatus, Len(strStatus) - 4) If strCriteria & "" = "" Then strCriteria = "(" & strStatus & ")" Else strCriteria = strCriteria & " And (" & strStatus & ")" End If End If
Single quotes works like a charm! Thank you so much!!! I think I can change the rest of the list boxes using this one as a guide.
Then I'm going to see if I can create some sorting options to add to this form. So I may be back. :/ But hopefully not. Hopefully I can figure out that part.
strYear = strYear & "[DateYear] = " & Me.lstYear.ItemData(varIte
to check if you have the syntax correct
debug.print strCriteria