Avatar of fabi2004
fabi2004
Flag 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

VBAMicrosoft Access

Avatar of undefined
Last Comment
fabi2004

8/22/2022 - Mon
Rey Obrero (Capricorn1)

place a space in "Or"

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

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

Open in new window

fabi2004

ASKER
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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Rey Obrero (Capricorn1)

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
fabi2004

ASKER
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.
Rey Obrero (Capricorn1)

see my last post
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
fabi2004

ASKER
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))'
Rey Obrero (Capricorn1)

what is the complete strCriteria?
better if you can upload a copy of the db
fabi2004

ASKER
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?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Rey Obrero (Capricorn1)

delete the entry on the filter and save the form
fabi2004

ASKER
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
Rey Obrero (Capricorn1)

upload a copy of your db
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
fabi2004

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

The form in question is the frmBookings3 form.

Thanks so much for helping me with this.
Day-Tracker-v19_be.zip
Rey Obrero (Capricorn1)

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Dale Fye

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

fabi2004

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

Thanks again.

-Fabi
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rey Obrero (Capricorn1)

@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.
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?
Rey Obrero (Capricorn1)

yes
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
fabi2004

ASKER
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?)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rey Obrero (Capricorn1)

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?
Rey Obrero (Capricorn1)

yes like what I posted for the lstMonth
Your help has saved me hundreds of hours of internet surfing.
fblack61
fabi2004

ASKER
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
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
fabi2004

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