Link to home
Start Free TrialLog in
Avatar of fabi2004
fabi2004Flag for United States of America

asked on

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 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

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

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

place a space in "Or"

strYear = strYear & "[DateYear] = " & Me.lstYear.ItemData(varItem) & " Or "

to check if you have the syntax correct
debug.print strCriteria
Avatar of 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

Open in new window

By the way, I also tried the following since there will never be any records prior to 2014.  But I'm getting the same problem.

If IsNull(Me.lstYear) Or Me.lstYear = "" Then 
    strYear = "([DateYear] > (" & 2014 & "))"
    strCriteria = strYear

Open in new window

try looking at the strCriteria string with
debug.print strCriteria
see what string was created

also, instead of using

If IsNull(Me.lstYear) Or Me.lstYear = "" Then

use
If Me.lstYear.ItemsSelected.Count = 0 Then
I selected, 2017 from the Year list and December from the month list.  It wrote:

([DateYear] like "*") And ([DateMonth] = 12)

It should have written ([DateYear] = 2017) And ([DateMonth] = 12)

It's ignoring my selections in the year list.
see my last post
using
If Me.lstYear.ItemsSelected.Count = 0 Then


I get
Run-time error '3075'
Syntax error (missing operator) in query expression '(And (DateYear] = 2017) And ([DateMonth] = 12))'
what is the complete strCriteria?
better if you can upload a copy of the db
I just noticed that there is code in the filter property of the form as I switch back and forth between the design and form views.

I haven't written anything to turn the filter off yet.  I wonder if it's just accumulating from previous iterations?
delete the entry on the filter and save the form
Alright, I did that. But I'm still getting the same error using

 If Me.lstYear.ItemsSelected.Count = 0 Then

I don't get the error using

If IsNull(Me.lstYear) Or Me.lstYear = "" Then

But then the selection is ignored
upload a copy of your db
Alight, that will take me a few minutes to re-combine the ME & BE.  I'll upload a copy asap.
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.

The form in question is the frmBookings3 form.

Thanks so much for helping me with this.
Day-Tracker-v19_be.zip
first, do  a debug compile and correct ALL errors..

use this codes for the lstYear

If Me.lstYear.ItemsSelected.Count = 0 Then
    strYear = "([DateYear] like '*')"
    strCriteria = strYear
Else
   For Each varItem In Me!lstYear.ItemsSelected

''' Use "Or" to get result from all years

    strYear = strYear & "[DateYear] = " & Me!lstYear.ItemData(varItem) & " Or "
Next varItem
    If Len(strYear) > 0 Then
        strYear = Left(strYear, Len(strYear) - 4)
'        strCriteria = strCriteria & " And (" & strYear & ")"
        strCriteria = "(" & strYear & ")"
    End If
   
End If

TEST this and we will continue with the rest of the codes
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 variant
Dim varYears as variant, varMonths
Dim strCriteria as string
varYears = NULL
for each varItem in Me.lstYear.ItemsSelected
    varYears = (varYears + ", ") & me.lstYear.itemdata(varItem)
Next
if isnull(varYears) = false then
    if me.lstYear.ItemsSelected.Count = 1 Then
        strCriteria = "([DateYear] = " & varYears & ")"
    else
        strCriteria = "([DateYear] IN (" & varYears & "))"
    endif
endif

varMonths = NULL
for each varItem in Me.lstMonth.ItemsSelected
    varMonths = (varMonths + ", ") & me.lstYear.itemdata(varItem)
Next
if 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 & "))"
    endif
endif

Open in new window

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.
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.

Thanks again.

-Fabi
@Fabi
you don't really need this part

    strYear = "([DateYear] like '*')"
     strCriteria = strYear

if there is none selected in lstYear, leaving the filter for the [DateYear] blank will return all years.
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?
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
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
Add  If strCriteria & "" = "" Then  ' to all the list boxes?
yes like what I posted for the lstMonth
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 box

For 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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

Rey, thank you so very much for your help!