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?
After building the remaining criteria
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
VBAMicrosoft Access
Last Comment
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".
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
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
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.C
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.C
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.
([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
ASKER
using
If Me.lstYear.ItemsSelected.C
I get
Run-time error '3075'
Syntax error (missing operator) in query expression '(And (DateYear] = 2017) And ([DateMonth] = 12))'
If Me.lstYear.ItemsSelected.C
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
better if you can upload a copy of the db
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?
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
ASKER
Alright, I did that. But I'm still getting the same error using
If Me.lstYear.ItemsSelected.C
I don't get the error using
If IsNull(Me.lstYear) Or Me.lstYear = "" Then
But then the selection is ignored
If Me.lstYear.ItemsSelected.C
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
ASKER
Alight, that will take me a few minutes to re-combine the ME & BE. I'll upload a copy asap.
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
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.C
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(varIte
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
use this codes for the lstYear
If Me.lstYear.ItemsSelected.C
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(varIte
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:
([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
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.
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.
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
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.
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.
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?
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?
yes
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?
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(varIt
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
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(varIt
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
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
ASKER
Add If strCriteria & "" = "" Then ' to all the list boxes?
yes like what I posted for the lstMonth
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)?
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
ASKER CERTIFIED SOLUTION
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.
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!
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!
strYear = strYear & "[DateYear] = " & Me.lstYear.ItemData(varIte
to check if you have the syntax correct
debug.print strCriteria