Multiple Search Criteria From Different Controls 1

[LowBrow]

I have done a fairly thorough review of this topic in the forums, but I have not a found one that addresses what I need to do, or least one that I interpret well enough.
I have a table [tblFlowNote] with the following fields
flownoteID, ClientID, FlowDate, FlowNote

I have a form [frmSearchMain] with a list box of available clients [listClients], a list box that accepts multiple clients selected from listClients [listSelClients], a check box [chkAllClients] which disables listClients (this is to allow the user to search all clients), Calendar Control 10.0 which sends selected dates to BeginDate and EndDate (for a date range search), and a text box [txtSearchCrit].

You can probably see where this is going. I want the user to be able to enter as little or as much search criteria to pull matching records. I have 'Search All Clients' and the date range pieces working, but I cannot get the list box or multiple criteria working. I believe they are related problems. The list box populates with a comma separtor.

I think I am having two primary problems. First, I hav found postings that allow users to use wild cards, but these seem to limit to a finite number of options. I mean, it appears you must code 'Like' statements for as many possible entries as the user may make. If I code up to three 'Like' statements and the user puts in four, it will not work. The second problem is using the list box. If I have more than client added to the list box, it returns no records. I think the code is reading the box as an whole search term, rather than parsing the entries by comma.
I know this is long-winded, but I wanted to give as complete descrition as possible. Any help is appreciated.
Thanks!

 

[Blorf]

I don't have a clear picture of the issue, but this may help.

Say your form control contains values like A, B, C, D

And you want to return records with either A, B, C, or D, but don't want seperate like conditions.

Assuming you are correct, then may I suggest an iif statement instead.

Using your own field names of course, try this:

InStr([YourFormControl], [YourField]) as test

And as a condition, >0

This will look for your field value within your form list.

Hope I understand correctly,
ChaZ

 

[FancyPrairie]

This FAQ faq181-5497 contains a function that will do what you want. It will return the where clause without the word Where. It handles 0 to many list boxes, combo boxes, text boxes, ranges, check boxes, and option groups. You only have to do 3 things to make it work.

1. Create a new module and highlight the code in the FAQ, copy it and paste it in your new module.
2. Set you tag properties as specified in the FAQ
3. Open your report as specified in the FAQ

 

[LowBrow]

Thanks to both of you! FancyPrairie, I think this is exactly what I need! I will post here on how things turn out.

 

[LowBrow]

Hi Fancy Prairie,
I have cut and pasted the code as you recommend, and when I set the call within the DoCmd line, I get an error on compiling "Expected variable or procedure, not module". Any ideas?

DoCmd.OpenForm "frmSearchRptSel", acPreview, , BuildWhere(Me)

I am using this to open a form that will display the records matching the users input, then the user will narrow their selections for the final report by checking boxes next to flow notes they wish to view.

 

[FancyPrairie]

You issued the OpenForm method rather than the OpenReport method.

 

[LowBrow]

I tried using the OpenReport method and it triggered the same error upon compiling. I was doing that to troubleshoot. The error is triggered on compiling before any form action is taken.

 

[LowBrow]

Do I need to make any specific declarations in the code for the form? Would that be triggering this error?

 

[FancyPrairie]

I created a new module and copied and paste the code into the new module, then compiled it with no errors. What did you name your module? Try naming it basBuildWhere.

 

[LowBrow]

I named it as BuildWhere. I read posts where this had come up, but couldn't see the forest for the trees. That did it!
Thanks again!

 

[LowBrow]

Hi again, FancyPrairie,
I am having one other issue with this code that maybe you can help with. The way this code is set, the user selects the items in the list box he or she wishes to search on. In my form, the user selects the clients from list a, and shifts them to list b. I do this, because I am dealing with a listing of over 200 client names, and this helps the user easily identify those that are selected, rather than having to scroll though and make multiple selections.
The problem is, that in order for the code you provided to work properly as I have it in place, the clients selected must be physically 'selected' (highlighted) in list b. I have been able to programmatically 'select' clients once they are in list b, but this is not elegant, and presentes its own problems (as with a user deselecting a given client).
I am trying to see where in your code I can have ALL the items listed in list b used as the criteria, rather than only those 'selected'. I hope this makes sense.

 

[FancyPrairie]

In the BuildWhere function there is a sub (gosub) labeled BuildListBox: Within this section, change this:

    If (ctl.MultiSelect) Then
        For Each varItem In ctl.ItemsSelected
            strWhere = strWhere & strFieldType & ctl.Column(ctl.BoundColumn - 1, varItem) & strFieldType & strSuffix
        Next varItem

        strWhere = Mid(strWhere, 1, Len(strWhere) - Len(strSuffix)) & ")) "

to this

    If (ctl.MultiSelect) Then
        For i = 0 To List15.ListCount - 1
            strWhere = strWhere & strFieldType & ctl.Column(ctl.BoundColumn - 1, i) & strFieldType & strSuffix
        Next varItem

        strWhere = Mid(strWhere, 1, Len(strWhere) - Len(strSuffix)) & ")) "

This is assuming that your list box is a multiselect list box.

 

[LowBrow]

What does 'List15' reference? It is triggering an error 'variable not defined'. I assumed it was referencing the list the selected items were being stored in (list2 in my form), but that did not work either.

It is a multiselect list box, set to 'extended'. Thanks again for all your help on this. Everything is working well, except for a couple bugs on my end.
This has been a great aid to me!

 

[FancyPrairie]

Sorry. In my haste, I copied and pasted my test code without fixing it. List15 should be ctl. Like this:

    If (ctl.MultiSelect) Then
        For i = 0 To ctl.ListCount - 1
            strWhere = strWhere & strFieldType & ctl.Column(ctl.BoundColumn - 1, i) & strFieldType & strSuffix
        Next varItem

        strWhere = Mid(strWhere, 1, Len(strWhere) - Len(strSuffix)) & ")) "

 

[LowBrow]

I changed 'Next varItem' to 'Next i', and it compiled properly (I got an error 'Invalid Next control variable reference').
However, when I run the form with the change I made, it will not use the clients listed in the box unless they are highlighted. Otherwise, it returns all records. If I highlight the client, it returns the appropriate records.

Does my change seem correct? Do you have any idea why this works in your test and not in mine?
Here is how it looks:

BuildListBox:

'*******************************************
'*  Determine Operator (=, >, Like, etc.)  *
'*******************************************
    
    strAndOr = vbNullString
    If (Len(strOperator) > 0) Then
        If (strOperator = "<>") Then strAndOr = " AND " Else strAndOr = " OR "
    End If

    If (Len(strOperator) = 0) Or (strOperator = "=") Then
        strWhere = strWhere & strAnd & " (" & strFieldName & " In ("
        strSuffix = ", "
    Else
        strWhere = strWhere & strAnd & " (" & strFieldName & " " & strOperator & " "
        strSuffix = ") " & strAndOr
    End If
                
        
    If (ctl.MultiSelect) Then
        For i = 0 To ctl.ListCount - 1
            strWhere = strWhere & strFieldType & ctl.Column(ctl.BoundColumn - 1, i) & strFieldType & strSuffix
        Next i

        strWhere = Mid(strWhere, 1, Len(strWhere) - Len(strSuffix)) & ")) "

    
    Else
        If (Len(strFieldValue) = 0) Then strFieldValue = ctl.Column(ctl.BoundColumn - 1)
        strWhere = strWhere & strFieldType & strFieldValue & strFieldType & strSuffix
    End If
    
    strAnd = " AND "
    
    Return

 

[FancyPrairie]

Looks like I missed a few pieces. Too many other things going on. Anyway, try this. In the function BuildWhere_ControlType the code should change from this:

            If (ctl.MultiSelect) And (ctl.ItemsSelected.Count > 0) And (ctl.Enabled) And (ctl.Visible) Then BuildWhere_ControlType = "ListBox"

to this

            If (ctl.MultiSelect) And (ctl.ItemsSelected.Count >= 0) And (ctl.Enabled) And (ctl.Visible) Then BuildWhere_ControlType = "ListBox"

 

[LowBrow]

That did it! Sweet! Thank you soooooo much!