Multi-Select LIstbox can it be searched easily?

[vb9063]

Cool thanks, is the bit in the IN('AB','AL') an OR function? Does it result in an output of the records that satisfy either as opposed to both??

 

[vbaInet]

Well said. It's like a string of ORs. The IN function is like a one-dimensional array.

 

[vb9063]

Ok, not sure whether I need to have it select only those that satisfy both or not argh...hmm..

 

[vbaInet]

Have a think, look at your results and compare it with what you would like to achieve. If you want the composite, just concatenate using the AND operator. Good thing you don't have thousands of postcodes so it would run smoothly.

 

[vb9063]

oo so would it end up like IN('AB' AND 'AL')? Or would that not work!

 

[vbaInet]

Nope!! Try it out and see.

 

[vb9063]

Oh Dear I think i have broken the search again it doesnt seem to send the filter to the output fields anymore...Doh!

 

[vbaInet]

Whoops!! You've got the code here, copy and paste it back.

 

[vb9063]

Could do but I did a few things on friday to my saved copy which I might not get back...i am a plonker!

 

[vbaInet]

Lesson learned. Backup before making drastic changes

 

[vb9063]

Ta daaaa!! After a whole day of fiddling I finally figured out what was wrong with the filter! There was a piece of code that set the recordsource="" !! I think I figured out how to concatenate everything together it looks much less complicated now..is this the best most efficient way to do it??? THanks v much for your help!

Private Sub cmdFilter_Click()
    Dim varItem As Variant, strWhere As String
 
    ' Initialise to zero length string
    strWhere = ""
 
   With SearchPostcode
       If .ItemsSelected.Count <> 0 Then
            For Each varItem In .ItemsSelected
            strWhere = strWhere & "Services.[ser_postcode].value IN ('" & .ItemData(varItem) & "') AND "
        Next
        End If
    End With
 
    With SearchContainer
       If .ItemsSelected.Count <> 0 Then
            For Each varItem In .ItemsSelected
            strWhere = strWhere & "Services.[ser_cont].value IN ('" & .ItemData(varItem) & "') AND "
        Next
        End If
    End With
 
    With SearchWasteType
       If .ItemsSelected.Count <> 0 Then
            For Each varItem In .ItemsSelected
            strWhere = strWhere & "Services.[ser_wastetype].value IN ('" & .ItemData(varItem) & "') AND "
        Next
        End If
    End With
 
 ' Strip off the trailing " And " text string
If Len(strWhere) > 0 Then strWhere = Left(strWhere, Len(strWhere) - _
    Len(" AND "))
 
    Me.Filter = strWhere
    Me.FilterOn = True
End Sub

 

[vbaInet]

What I gave you was concatenating the IN() items whereas yours is concatenating a bunch of IN()s. Before the Me.Filter line put Msgbox strWhere and you will see what I mean. Simply follow what was given.

Two more things:
1. Avoid "open-ended" or one-line IF statements, close them properly with an END IF. If you want one-line IFs then use the IIF statement, however, the IIF will evaluate both arguments.
2. There's no point turning on the form's filter if a filter was not chosen. So enclose those two lines in your Len(strWhere) IF block.

 

[vb9063]

What I gave you was concatenating the IN() items whereas yours is concatenating a bunch of IN()s. Before the Me.Filter line put Msgbox strWhere and you will see what I mean. Simply follow what was given.

Two more things:
1. Avoid "open-ended" or one-line IF statements, close them properly with an END IF. If you want one-line IFs then use the IIF statement, however, the IIF will evaluate both arguments.
2. There's no point turning on the form's filter if a filter was not chosen. So enclose those two lines in your Len(strWhere) IF block.

Hi vbaInet,
Thanks very much, you said that to make the search only return answers based on exactly what the user selects (instead of an OR which the IN('AB','AL') creates) to concatenate them...so I tried doing it and came up with the code above...so is there another way of doing it cos you said that when I asked if IN ('AB' AND 'AL') would work you said no...

Thanks!

 

[vbaInet]

I see what you're getting at. In your case, I think you should do a string of INs for each listbox because they are multivalued. Then concatenate the INs with ANDs. So:

IN('AB', 'BB', 'BC') AND IN(container list) AND IN(waste type list)

Will that not work for you?

 

[vb9063]

I see what you're getting at. In your case, I think you should do a string of INs for each listbox because they are multivalued. Then concatenate the INs with ANDs. So:

IN('AB', 'BB', 'BC') AND IN(container list) AND IN(waste type list)

Will that not work for you?

Morning vbaInet,
Yes that did work when you gave it to me the other day, but then I realised that it needed to AND the bits within the INs so that I got ANDs for those instead of ORs because you said the other day that the (,) creates ORs instead of ANDs...

 

[vbaInet]

Good morning! If you that's what you truly want then just get rid of the INs and concatenate everything using ANDs.

 

[vb9063]

Good morning! If you that's what you truly want then just get rid of the INs and concatenate everything using ANDs.

Thanks vbaInet, I will give it a go! Is there a way to set the output field of the filter (mine is outputting the name of the supplier into a text field) to "no criteria match your search.." if there are no suppliers that meet all the criteria instead of displaying nothing? Would you set the control source of the text field to something? What is it the expression that gives the text box its info. I have tried fiddling with me.ser_sup.controlsource (where ser_sup is the name of the supplier linked to the service) but am not getting very far! THanks for all your help and patience!

 

[vbaInet]

You could create a dummy record with that data and display it that way or just have a label at the top that displays a count of how many records are returned after a search.

 

[vb9063]

You could create a dummy record with that data and display it that way or just have a label at the top that displays a count of how many records are returned after a search.

Morning vbaInet,
I have tried to use the following code to set the controlsource of a textbox above the search results to count the suppliers:

Me.txtSearchResults.ControlSource = "=Count([ser_sup])"

It works but it is slower than the filter and only pops up after the filter has displayed the results which looks strange. Is there a way to make it display at the same time??

I have also tried using variations of the below code which doesn't work at all when i put it in VBA

Me.txtSearchResults.ControlSource = ""="&"Your Search returned"&"Count([ser_sup])"&"results""

so that I can get a sentence with the result.

I used this code in the actual controlsource but I also get the delayed reaction..is there a fix for this and I couldnt figure out how to put the same code with the text extensions into VBA??

="your search returned " & Count([ser_sup]) & " results"

Thanks again, vb

 

[vbaInet]

Actually it's not strange for it to show a count after all the records have appeared because it is counting line by line.

If you want an instantaneous count then perform a RecordCount on the RecordsetClone of the form and set the returned count to the VALUE of the textbox, not control source. You will apply this on your Filter and Reset Filter command buttons right after Me.FilterOn. Research those methods.