none
Creating a Selectable/Customized Search Parameter Form/Report RRS feed

  • Question

  • Hey everyone.  So I'm back with another issue, this one might be too big an idea to create...  So I have to provide a form that allows the user to select the specific fields they want to do a search.  So, for example, say we have a form that has a list of fields as checkboxes.  The user would go through and select which fields they want and then click a button.  That button then opens a form that provides input options (which would include list boxes, combo boxes, option buttons or check boxes, maybe even text boxes and date boxes) that match the selected search parameters chosen.  The user can input the variables into the fields and click a button to search or a button to output to a report, or maybe they click the search button that opens a subform and can then click a button to output the search to a report.

    Even though I know the search field options, it gives me a headache trying to wrap my mind around it because of the seemingly endless options that would need accounted for with this.  I've been given a list of 18 (yes, 18!) field options to build this upon.  So I'm here to ask for some help and direction on how to accomplish this.

    Thanks,

    R'C

    Tuesday, August 22, 2017 2:25 PM

Answers

  • Hi,

    One approach to search "all" fields is to concatenate the values in all the fields and then use the wildcard character or the InStr() function to search the entered parameter or keyword in the record. For example, if the user enters "help" as a search term, and you want to search five fields, it might look something like this:

    WHERE [Field1] & [Field2] & [Field3] & [Field4] & [Field5] Like "'*" & strSearch & "*'"

    Hope it helps...

    • Marked as answer by R'C Wednesday, August 30, 2017 3:34 PM
    Friday, August 25, 2017 8:13 PM
  • Hi,

    To break up a line of code in VBA, you can use the continuation characters " _" (a space followed by the underscore). If it doesn't work, you can break down the variable assignment by appending the new value to itself. For example:

    var = var & 1
    var = var & 2
    var = var & 3
    and so on...

    Hope it helps...

    • Marked as answer by R'C Wednesday, August 30, 2017 3:34 PM
    Friday, August 25, 2017 8:49 PM
  • The sort of questions you have cited as examples of those that users might ask suggests that they are aware of in which column a value, or partial value, might be.  In which case, to do this with a query I would not group the columns in the way you are doing as it could well return specious mismatches.  I would have a separate parameter for each column and make each optional by testing for NULL.

    The basis of this is that in the WHERE clause each parameter is tested in this way:

    WHERE (SomeColumn = [some parameter]
      OR [some parameter] IS NULL)
    AND (SomeOtherColumn = [some other parameter]
      OR [some other parameter] IS NULL)
    AND etc

    You can of course use:

        SomeColumn LIKE "*" [some parameter] & "*"

    where it is appropriate to use pattern matching rather than testing for equality.  Bear in mind, however, that even when applied to a single column this can still result in specious mismatches, though the likelihood is reduced in comparison with doing so on the concatenated values from a group of columns.  Moreover, the LIKE operator does not allow use of the indexes, so can reduce performance significantly.

    The logic behind this approach is extremely simple and consequently, given good indexing in the table, very efficient.  It also has the advantage of not having to bother about the data type of the column in question, so unlike when building an SQL statement in code, consideration does not need to be given as to whether the values need delimiting or not.  

    Each OR operation is enclosed in parentheses to force it to evaluate independently.  These parenthesized expressions are then tacked together with AND operations.  The way it works is that each parenthesized OR expression will evaluate to TRUE for each row where the value in the column is that of the parameter, or if the parameter is left empty (NULL),  for every row.  By virtue of the AND operations  the WHERE clause as a whole will evaluate to TRUE for those rows where all of the parenthesized expressions evaluate to TRUE, so those rows will be returned.

    Note that when you do this, parameters should only be declared if they are of DateTime data type.  If other types were declared they could never be Null.  DateTime parameters are unusual in this respect, and it's always advisable to declare them to avoid their being misinterpreted as arithmetical expressions rather than dates.

    When building a query like this, the basic unrestricted query can be built in query design view, but the WHERE clause should always be written and, most importantly, saved in SQL view.  This applies to the initial saving of the query, and if any subsequent amendments are made.  If it's saved in design view Access will move things around and at best the logic will be obscured, at worst it might become too complex to open.  It's a good idea to save the SQL of such queries in a text file in Notepad or similar, as if anything does go wrong you then have something to copy and paste back into the query designer in SQL view.

    Note BTW that if searching on the basis of a date range this can be made a closed range or open ended in either direction by treating the start and end date parameters independently, rather than within a BETWEEN….AND operation:

     WHERE (DateColumn >= [some parameter]
      OR [some parameter] IS NULL)
    AND (DateColumn < [some other parameter]+1
      OR [some other parameter] IS NULL)

    Beyond this I don't think I can contribute anything more of use to you, so I shall not be monitoring this thread any further.

    Ken Sheridan, Stafford, England

    • Marked as answer by R'C Wednesday, August 30, 2017 3:34 PM
    Tuesday, August 29, 2017 10:54 PM

All replies

  • Hi,

    A simpler design, if these fields are from a table, is maybe to use a multi-select Listbox rather than multiple checkboxes.

    You can set the Row Source of the Listbox to the name of the table and then set the Row Source Type to Field List.

    Hope it helps...


    • Edited by .theDBguy Tuesday, August 22, 2017 2:32 PM
    Tuesday, August 22, 2017 2:31 PM
  • That's a good idea.  Only issue is that some of the parameters are from a joined table.  I suppose I could make 2 list boxes, 1 for each table.  Is there a way to combine them into the list box or do I need to create a query specifically for the fields...
    Tuesday, August 22, 2017 2:39 PM
  • Duh, hey I got that part solved sorry to ask such a dumb question....I knew the answer right as I clicked reply lol....
    Tuesday, August 22, 2017 2:42 PM
  • Hi,

    I just gave it a try, and it turns out, you can use a query joining the two tables and still show the fields from each of them.

    However, there might be an issue in determining which field came from which table when it comes time for you to code the "search" functionality.

    Tuesday, August 22, 2017 2:45 PM
  • Yeah, the search function...that's the part I'm more questionable about.  How on earth do I make a form that will have only the search fields the user selects in the list box.  There's such a deep level of combinations.  Off the top of my head, I'm thinking about building 1 form that includes every search input option that will be visible only if the user has selected those input fields to use as search parameters....what do you think?
    Tuesday, August 22, 2017 2:50 PM
  • Hi,

    I don't think it would be a problem coding the search function if you kept to a single table source for each Listbox of fields. It was when you thought about combining tables in one list of fields I was concerned about. Otherwise, if you know which field came from which table, you can simply construct the SQL statement for the search result.

    Hope it helps...

    Tuesday, August 22, 2017 3:13 PM
  • I understand that, but I'm still stuck in how I can make a form where the controls on it are determined by the user.  It's like building a modular search form, where the criteria fields that are available for input are determined by the user selecting those fields prior to the search form appearing.  I'm not sure I'm making sense...So, with the list box of field names, for example: the user may select any combination of those fields, as you know, but then when the user is done selecting those fields a button is clicked to open the search form, which contains only those fields that were selected by the user...
    Tuesday, August 22, 2017 6:37 PM
  • Hi,

    Check out some of the search form demos at UtterAccess Code Archive to see if any of them sparks an idea suitable for your situation.

    Hope it helps...

    Tuesday, August 22, 2017 6:56 PM
  • I did do something along these lines many years ago where a combo box listed all the columns on which a search could be undertaken, and when a column was selected a further control (or two or more controls when defining a date range or similar) was made visible on the basis of which column had been selected.  This process was repeated until the full expression had been completed, at which stage a 'Confirm Search' button was pressed.  I also included buttons for inserting Boolean AND, NOT, OR and XOR operators into the expression, and for inserting closing and opening parentheses.  As the expression was built up it appeared in another control in the form as a plain English equivalent of the actual expression built progressively behind the scenes.

    This gave very flexible functionality, but it did require the users to be able to build a valid logical expression.  This was not a problem in my case, as I was head of a group of specialised scientific officers who were all familiar with Boolean logical expressions in our real work, but it might not be so easily understood by more mainstream users.  For other users of the data I did design a much simpler interface with a limited number of parameters.

    I would rarely design such an interface now, however, but would be more likely to simply present all the parameters in a form and make each of them optional by making the bound form or report's RecordSource a query which referenced each control as a parameter. 18 parameters can easily be accommodated in this way.  The basis of this is that in the form or report's query's WHERE clause each parameter is tested in this way:

    WHERE (SomeColumn = <some parameter>
      OR <some parameter> IS NULL)
    AND (SomeOtherColumn = <some other parameter>
      OR <some other parameter> IS NULL)
    AND etc

    Each OR operation is enclosed in parentheses to force it to evaluate independently.  These parenthesized expressions are then tacked together with AND operations.  The way it works is that each parenthesized OR expression will evaluate to TRUE for each row where the value in the column is that of the parameter, or if the parameter is left empty (NULL),  for every row.  By virtue of the AND operations  the WHERE clause as a whole will evaluate to TRUE for those rows where all of the parenthesized expressions evaluate to TRUE, so those rows will be returned.

    Note that when you do this, parameters should only be declared if they are of DateTime data type.  If other types were declared they could never be Null.  DateTime parameters are unusual in this respect, and it's always advisable to declare them to avoid their being misinterpreted as arithmetical expressions rather than dates.

    When building a query like this, the basic unrestricted query can be built in query design view, but the WHERE clause should always be written and, most importantly, saved in SQL view.  This applies to the initial saving of the query, and if any subsequent amendments are made.  If it's saved in design view Access will move things around and at best the logic will be obscured, at worst it might become too complex to open.  It's a good idea to save the SQL of such queries in a text file in Notepad or similar, as if anything does go wrong you then have something to copy and paste back into the query designer in SQL view.

    You'll find a simplified example of this in DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the section on 'Retrieving data from the database' includes a form where values can be selected in any one, both or neither of two combo boxes to restrict a report an city and/or employer columns.  Leaving both empty returns all rows.  Extending the number of parameters merely means adding further parenthesized OR expressions in the query, as described above.  The report includes the SQL statement for the query in its header.  Note that the use of the AND operator between each parenthesized expression means that the combination of parameters is restrictive.  If you wanted it to be expansive then the OR operator would be used between each parenthesized expression.

    Ken Sheridan, Stafford, England

    Tuesday, August 22, 2017 11:05 PM
  • Ken,

    I'm working through this with some difficulty, but at this point it's still form design.  I do want to ask if you can possibly provide some help with using dates in a search.  For example, I'd like to provide a search option based on an Installation Date field where the user can find a date that falls between 2 dates, e.g., start date and end date.  In addition to that, I've also considered using an option group to present comparison operators (=, <, <=, etc.) the user can select along with a text box date field.  I'm not sure which is better, easier, or more likely to work out in this situation.  Any way you can give me some examples or guidance with this?

    Wednesday, August 23, 2017 6:02 PM
  • I understand that, but I'm still stuck in how I can make a form where the controls on it are determined by the user.

    Hi R'C,

    To get some ideas, this is the Search function that I use.

    The demonstration is that you can work with user defined fields with user defined conditions, without an overwhelming number of all kind of possibilities.

    Per field you can choose an appropriate selection criterion that is fieldtype dependant. The underlined fields indicate that this is a link to a different table using the FK.

    Only those fields are used in the WHERE clause that have a not-null-value. This WHERE clause overwrites the WHERE clause of the sql-string of "any" continuous form.

    Imb.

    Wednesday, August 23, 2017 7:00 PM
  • I do want to ask if you can possibly provide some help with using dates in a search.  For example, I'd like to provide a search option based on an Installation Date field where the user can find a date that falls between 2 dates

    If you are intending to selectively show the controls in which to insert values, as in the example from years back which I described, then, then, when selecting the column on which the search by a date range is based, you would make two controls visible, txtStartDate and txtEndDate say, and the code to build the string expression for this would be along these lines

    strFilter = strFilter & " And [InstallationDate] >= #" & Format(Me.txtStartDate,"yyyy-mm-dd") & _
        "# And [InstallationDate] < #" & Format(Me.txtStartDate,"yyyy-mm-dd") & "#+1"

    when you've finished building the value of the strFilter variable by adding other criteria like this you'd strip off the redundant leading ' And ' with:

    strFilter = Mid(strFilter,6)

    If, on the other hand, you are going for maximum flexibility by allowing users to add the operators, I used a separate button for each, but an option group would work fine.  In this case you'd show just one control to enter the date, txtDate say.  If the user selects the >= operator say, the code would add this to the string expression:

    strFilter = strFilter & " >= "

    Then when they enter the start date for a range the code would be:

    strFilter = strFilter & "#" & Format(Me.txtDate,"yyyy-mm-dd") & "#"

    Then if they select the <= operator the code would be:

    strFilter = strFilter & " >= "

    If they then select the AND operator:

    strFilter = strFilter & " AND "

    And when they enter the end date for the range:

    strFilter = strFilter & "#" & Format(Me.txtDate,"yyyy-mm-dd") & "#"

    There would be no need to strip off any redundant leading ' And ' in this case of course because only the necessary operators would be added to the expression by the user.

    However, while this gives maximum flexibility, this does put a lot of onus on the user, who must be able to build coherent logical expressions.  This was not a problem with my section's staff, who, as specialists in our field of work, were all literate in this respect, but I would not have expected staff from most other sections of the department to be able to do this, which is why I gave them a simplified interface with limited self-evident choices.


    Ken Sheridan, Stafford, England

    Wednesday, August 23, 2017 8:04 PM
  • So you list every field and allow the user to select the parameters they want...?  I apologize lmb, but I'm not fluent in your language.  I can figure out some of the words easy enough but not others.  Is that German?  So I'm not sure I get the full picture, but it looks like you list all values in a field then allow them to somehow build a search around the field value with available logic operators the user can choose...
    Wednesday, August 23, 2017 9:03 PM
  • This should prove quite helpful.  Thank you, Ken...
    Wednesday, August 23, 2017 9:03 PM
  • So you list every field and allow the user to select the parameters they want...?  I apologize lmb, but I'm not fluent in your language.  I can figure out some of the words easy enough but not others.  Is that German?

    Hi R'C,

    It is Dutch. Yes, the users select the parameters they want.

    This form is "standard build in" in any continuous form, in any applicatrion.

    This example is part of a genealogical database to find a Person..., mostly a hell of a job, especially if there are quite a lot Persons (> 100.000) in the database with often the same Lastname.

    About the fieldnames:
    Achternaam = Lastname
    Tussenvoegsel = some insertion Lastname before the real Lastname
    Voornamen = Christian names
    Status = some kind of event, like Birth, Marriage, Divorce, Pass away
    Plaats = City of that event
    Geslacht = Sexe
    Roepnaam = Firstname, Nickname
    Opmerking = Remark
    Vader = Father
    Moeder = Mother
    RolA = Date/Event of first event
    Levenloos_geboren = Born lifeless
    Ouders_check = An indication whether a "relation" between the parents is described
    Stam = Belonging to which family hierarchie

    About the buttons below:
    Zoek = (Start) Search
    Alles = All
    Velden wissen = Clear fields (for next search)
    Geselecteerd = Selected; each table has a Selection field, that can be ticked independantly of other definitions
    Voorselectie = Pre-selection. Each continuous form can have a number frequently used WHERE clauses. You can selct one of these with or without the current selection
    Id = list of id_values extracted from some other query.

    About the "Selecteer conditie":
    beginnend = starting with
    gelijk = equal
    bevat = contains
    eindigend = ending
    joker = use wildcharacters
    AND-woorden = one or more strings that must all occur in the value
    OR-woorden = one or more strings, of which at least one must occur in the value
    tussen = between
    Null = Null
    Not NUll = Not Null

    The nice thing is that additional conditions can easily be added.

    Well, this was the first lesson Dutch.

    Imb.

    Wednesday, August 23, 2017 9:45 PM
  • OK, I'm getting way ahead of myself, I think.  lmb, apologies for not recognizing the language.  Wish I was better versed in the world where things like that matter.

    Overall, I have 4 combo boxes, 6 multi-select list boxes, 4 sets of range text boxes (between date1 and date2, between age1 and age2, etc.), and I have 1 option group that includes 5 comparison options for 1 of 3 check boxes. How on earth do I account for that in the query?  I get writing the query so that we have the WHERE clause with the OR option in parenthesis...but how do you do that if there is the possibility of having not just combo boxes, but all these other control types (i.e., combo boxes & list boxes & option group or comparison text boxes with list boxes, etc.).  Is this something that would work better if the string was built on the fly as with my previous problem?  Should I have some combination of an invisible text box to capture the SQL in real time to pass it to after update events?  That's what is really confounding me....

    Thursday, August 24, 2017 2:44 PM
  • Hi,

    Have you had a chance to take a look at some of the examples I pointed to earlier to see how they solved the same problem?

    Personally, I would just build the SQL string on the fly by processing all the form objects one at a time.

    Just curious...

    Thursday, August 24, 2017 3:42 PM
  • Hi DB, yes I looked at some.  Although I didn't find much when it came down to having so many controls combined.  I have one I've been looking at that seems to have everything except the multi select lists and option groups.  I'm thinking you're right about building the string on the fly.  That's what I'm attempting now.  I'm trying to just break the options down so that they will work one by one then I'm hoping to come back to someway of building the SQL string.  I'm a little worried about not having all the controls cascaded downward, but I think I can handle that by having a pop-up declare no matching results found or similar.
    Thursday, August 24, 2017 3:59 PM
  • I think building the SQL string on the fly as you process each selected criteria gives you more control than trying to add all possible combinations in a query. It would be more confusing in the long run.

    Just my 2 cents...

    Thursday, August 24, 2017 4:04 PM
  • OK, I can't get this working.  I've borrowed elements from different examples in this, but I'm just at a point where it's hard to see straight from all the code I've been looking at for this.  I've used AND as the conjunction but I think I need to use OR.  I really wish there was something like an "and/or" conjunction instead of one or the other.  I'm going to try to get another sample put together.  Until then, this is the code I've got so far.  I just keep getting syntax errors (I fixed what I can find).  If you see anything that's wrong (as I know there is) please feel free to advise a fix...

    Private Sub cmdFilterAll_Click()

                            
        Dim StrSource As String                'End date only   = all dates up to (and including this one).
        Dim strSQL As String                  'The criteria string.
        Dim vItem As Variant
        Dim lngLen As Long                      'Length of the criteria string to append to.
        Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
        
        '***********************************************************************
        'Look at each search box, and build up the criteria string from the non-blank ones.
        '***********************************************************************
        
        If Not IsNull(Me.cboCompany) Then
            strSQL = strSQL & "([Company] = """ & Me.cboCompany & """) AND "
        End If
        
        If Not IsNull(Me.cboProjectManager) Then
            strSQL = strSQL & "([ProjectManager] = """ & Me.cboProjectManager & """) AND "
        End If
        
        If Me.cboMaintenanceItem = -1 Then
            strSQL = strSQL & "([MaintenanceItem] = True) AND "
        ElseIf Me.cboMaintenanceItem = 0 Then
            strSQL = strSQL & "([MaintenanceItem] = False) AND "
        End If
          
        If Not IsNull(Me.cboComponentLocation) Then
            strSQL = strSQL & "([ComponentLocation] = """ & Me.cboComponentLocation & """) AND "
        End If

        If Not IsNull(Me.txtWildCardSearch) Then
            strSQL = strSQL & "[tblStudyRecords] Like ""*" & Me.txtWildCardSearch & "*"") AND "
        End If
        
    If Not IsNull(Me.lboSite) Then
            strSQL = "WHERE (((tblSites.Site) IN (SELECT [Site] FROM [tblSites] WHERE [tblSites].[Site] = "
                For Each vItem In Me.lboSite.ItemsSelected
            strSQL = strSQL & "'" & Me.lboSite.ItemData(vItem) & "'" & ","
    Next vItem
    End If

    If Not IsNull(Me.lboLocation) Then
        strSQL = strSQL & " OR [tblStudyRecords].[Location] IN (SELECT [Location] FROM [tblLocations] WHERE [Location] ="
            For Each vItem In Me.lboLocation.ItemsSelected
        strSQL = strSQL & Me.lboLocation.ItemData(vItem) & ","
    Next vItem
    End If

    If Not IsNull(Me.lboDivision) Then
        strSQL = strSQL & " OR [tblStudyRecords].[Division] IN (SELECT [Division] FROM [tblDivisions] WHERE [Division]="
            For Each vItem In Me.lboDivision.ItemsSelected
        strSQL = strSQL & Me.lboDivision.ItemData(vItem) & ","
    Next vItem
    End If

    If Not IsNull(Me.lboDescription) Then
        strSQL = strSQL & " OR [tblStudyRecords].[Description] IN (SELECT [Description] FROM [tblDescriptions] WHERE [Description]="
            For Each vItem In Me.lboDescription.ItemsSelected
        strSQL = strSQL & Me.lboDescription.ItemData(vItem) & ","
    Next vItem
    End If

    If Not IsNull(Me.lboMfr) Then
        strSQL = strSQL & " OR [tblStudyRecords].[Manufacturer] IN (SELECT [Manufacturer] FROM [tblStudyRecords] WHERE [Manufacturer]="
            For Each vItem In Me.lboMfr.ItemsSelected
        strSQL = strSQL & Me.lboMfr.ItemData(vItem) & ","
    Next vItem
    End If

    If Not IsNull(Me.txtDate1) Then
        strSQL = strSQL & "([InstallationDate] >= " & Format(Me.txtDate1, conJetDate) & ") AND "
    End If

    If Not IsNull(Me.txtAge1) Then
        strSQL = strSQL & "([Age] >= " & ") AND "
    End If

    If Not IsNull(Me.txtLifespan1) Then
        strSQL = strSQL & "([Lifespan] >= " & ") AND "
    End If

    If Not IsNull(Me.txtRemLife1) Then
        strSQL = strSQL & "([RemainingLife] >= " & ") AND "
    End If

    If Not IsNull(Me.frmCompCosts) Then
        Select Case (Me.frmCompCosts)
            Case 1
                strSQL = strSQL & "([Cost] " & Me.frmCompOperators.Value & Me.txtAmount & ") AND "
            Case 2
                strSQL = strSQL & "([SubTotalCost] " & Me.frmCompOperators.Value & Me.txtAmount & ") AND "
            Case 3
                strSQL = strSQL & "([TotalCost] " & Me.frmCompOperators.Value & Me.txtAmount & ") AND "
        End Select

       
        lngLen = Len(strSQL) - 5
        If lngLen <= 0 Then
            MsgBox "No criteria", vbInformation, "Nothing to do."
        Else
            strSQL = Left$(strSQL, lngLen)
            
            Debug.Print strSQL
         
            Me.Filter = strSQL
            Me.FilterOn = True
        End If
    End Sub

    Thursday, August 24, 2017 9:38 PM
  • Hi,

    Whether to use AND or OR depends on the result you are looking for. If you must use both, you'll need to make sure the operational precedence is established by proper placement of brackets in the SQL string.

    Do you see anything wrong in the resulting strSQL variable when you take a look at the result of the Debug.Print strSQL line?

    Thursday, August 24, 2017 9:45 PM
  • One thing I do notice about your code is that, when processing a multi-select list box, you are applying the IN operator to a subquery.  This is unnecessary, you can simply apply it to a value list built as the code loops through the list box's ItemsSelected collection.  The following is an example from my MultiSelect demo:

        Dim varItem As Variant
        Dim strEmployeeIDList As String
        Dim strCriteria As String
        Dim ctrl As Control

        Set ctrl = Me.lstEmployees

        If ctrl.ItemsSelected.Count > 0 Then
            For Each varItem In ctrl.ItemsSelected
                strEmployeeIDList = strEmployeeIDList & "," & ctrl.ItemData(varItem)
            Next varItem

            ' remove leading comma
            strEmployeeIDList = Mid(strEmployeeIDList, 2)

            strCriteria = "EmployeeID In(" & strEmployeeIDList & ")"

            DoCmd.OpenReport "rptEmployees", _
                View:=acViewPreview, _
                WhereCondition:=strCriteria
        Else
            MsgBox "No employees selected", vbInformation, "Warning"
        End If

    In this case the code filters a report, but it could equally well build part of an SQL statement's WHERE clause.  Note also how, rather than examining the control for Null, the Count property of its ItemsSelected collection is examined to  determine if any items are selected.

    If you are getting runtime errors, or if the code is not doing what you expect, then debug the code by setting a breakpoint on an early executable line in the procedure. Then click the button and step into the code line by line with the F8 key.  Examine the value of variables as you progress through the code either by hovering over the variable name in the code with the mouse pointer, or by printing the variable's value in the debug (aka 'immediate') window with:

    ? NameOfVariable

    If the value of the variable is not what you expect then you need to examine the code up to that point to determine why, and what changes you need to make to fix it.

    If the code raises a runtime error at some stage, the error will be reported when you execute the line in question, which will help you diagnose and fix the error.

    As regards the Boolean AND and OR operators you might like to take a look at BooleanDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file provides an introduction to Boolean logic in queries, using data from Northwind as its examples.

                                    

    Ken Sheridan, Stafford, England

    Thursday, August 24, 2017 10:53 PM
  • Is this something that would work better if the string was built on the fly as with my previous problem?

    Hi R'C,

    Yes, that is the way I work: build the string on the fly.

    When clicking the Zoek (Search) button, the code steps through all the "Waarde" controls, and if something is filled in, that control is processed. I check on   "Waarde" > ""  , in that case I both discard a Null-value and a ZLS.

    Depending on the fieldtype and the selection criterion I extend the where_string:

        where_string = where_string & " AND " & <field name><operator><value>

    If there are some OR's or other parts in relation to the prevalence) then parentheses can be added:

        where_string = where_string & " AND (" & <field name><operator><value> & ")"

    As you can see, I start every extension with " AND ". At the end of the looping throuh all the fields, the where_string = "" (no selections made) or starts with " AND ... ". For use as filter, you drop the first " AND ", for use in a complete sql_string you change the first " AND " in " WHERE ".

    You still have to take some measures with Boolean fields. "Not selected" is something different from "False". You need some tri-state boolean, or some text that is converted to True or False (as I do).

    In this way (at least) I have a manageable system, where the user can make almost any choice he can think of, that is easily extended for new search criteria.

    Imb.

    Friday, August 25, 2017 7:34 AM
  • Found some basic errors that I corrected so I'll repaste the code.  Also, I added the form (frmCustomSearch) to my Sample2 database example that can be downloaded here:

    https://1drv.ms/u/s!Akvvcs6GUyjrhhW97X7n7wDDSyB0

    Ken, I still need to go in and make those changes you advised.

    lmb, I have my checkboxes as part of an option group, which doesn't allow the Triple State property, I guess.  It's made me wonder if I should change the option group into a simple frame only with the check boxes independent of it.

    So if the only way to have both AND & OR is to build both options into the code in something of a nested structure, is that right?

    Right now, my Filter button tells me there's no criteria whenever I use any controls other than the Date range boxes and then it returns 0 results even if there is a match. 


    • Edited by R'C Friday, August 25, 2017 4:46 PM
    Friday, August 25, 2017 4:45 PM
  • So if the only way to have both AND & OR is to build both options into the code in something of a nested structure, is that right?
    Can you clarify what you mean by the above?  A Boolean AND operation will normally operate on two columns:

    WHERE Contact = "Ken" AND City = "Stafford"

    which would return me.  For obvious reasons it cannot operate on a single column:

    WHERE Contact = "Ken" AND Contact = "Fiona"

    The same contact cannot be both me and my wife, Fiona.  An OR operation can operate on two columns:

    WHERE Contact = "Ken" OR Contact = "Fiona"

    which would return both me and my wife.  It can also operate on two columns:

    WHERE Contact = "Ken" OR City = "Newport"

    which would return both me and my son, Gavin, who lives in Newport (along with all the other contacts named Ken and all other contacts living in Newport of course.

    I don't follow what you mean by 'both AND & OR' however?



    Ken Sheridan, Stafford, England

    Friday, August 25, 2017 5:32 PM
  • OK, so typing it out made me understand how redundant what I'm saying actually is.  I don't know why I couldn't see that until I typed it, so really forget it because I just wasted our time on it.  I apologize.  For the record...I was thinking that maybe I need to find a specific component where I don't know the actual name that it's been entered under, but I do know what Division it should be and I know what Location it should be in.  So when I run the search, I can see all results that either match that Division OR Location, and the results that match Division AND Location in the same record.

    Hopefully, that's today's only goof for me!

    Friday, August 25, 2017 5:46 PM
  • Here are a couple examples of the SQL my searches create:

    In searching for a specific company and all records that contain "yes" in the maintenance item field:

    ([Company] = "Company1")

    When I search just for the company, I keep getting:

    ([Company] = "Company1") AND ([InstallationDate] >= ) AND ([Age] >= ) AND ([Lifespan] >= ) AND ([RemainingLife] >= )

    So am I missing the beginning of the string (e.g., SELECT), as well as it seems even with the range fields being blank it's reading them as NOT NULL even through I use "If Not IsNull" in the code...

    Also, sometimes, the entire form goes blank when I click the Filter button...
    • Edited by R'C Friday, August 25, 2017 5:54 PM
    Friday, August 25, 2017 5:52 PM
  • Hi,

    Looks like your controls are not Nulls, so you're getting empty criteria rather than skipping them. In other words, if you only search for company, the criteria should only have:

    "([Company] = 'Company1')"

    Otherwise, if you're getting all the other conditions in your criteria, then your checks for Nulls were not sufficient. If so, you might try using other forms of checking the condition such as:

    If Me.txtAge1 > "" Then

    or

    If Nz(Me.txtAge1,"") <> "" Then

    or

    If Len(Nz(Me.txtAge1,"") > 0 Then

    or

    If Me.txtAge1 & "" <> "" Then

    etc.

    Hope it helps...


    • Edited by .theDBguy Friday, August 25, 2017 6:01 PM
    Friday, August 25, 2017 6:00 PM
  • The string expression  for the criteria should just be:

    ([Company] = "Company1")

    So you need to debug your code line by line after setting a breakpoint to see why the other column names and >= operators are being added unnecessarily.

    As regards the lack of a SELECT clause, this is something else for which debugging the code should help you to identify a reason.  Systematic debugging of code is just one of those things you have to expect when writing non-trivial code.

    Ken Sheridan, Stafford, England

    Friday, August 25, 2017 6:12 PM
  • Understood.  I'm in process of replacing the code for the list boxes.  Tried it after replacing the lboSite code and encountered the Compile Error: Argument Not Optional with ".ItemsSelected" in the following:

    Set Ctrl = Me.lboSite
    If Ctrl.ItemsSelected > 0 Then
    For Each vItem In Ctrl.ItemsSelected
    strSiteList = strSiteList & "," & Ctrl.ItemDate(vItem)
    Next vItem
    strSiteList = Mid(strSiteList, 2)
    StrSQL = "Site In(" & strSiteList & ")"
    End If

    Friday, August 25, 2017 6:18 PM
  • Hi,

    You are probably getting an error because I think ItemsSelected is a collection. So, to find out if it contains any selections, you'll have to use the Count property. For example:

    If Ctrl.ItemsSelected.Count > 0 Then

    Hope it helps...

    Friday, August 25, 2017 6:30 PM
  • I think part of the problem is that I'm using code that is partly written with the subform filter in mind and partly as a query.  So which works better with extended list boxes?  Because I'm getting an error now that states Run-Time Error 3075: In operator without () in query expression 'Site In'.  So I'm wondering if I need to change the line "StrSQL = "Site In(' & strSiteList & ")" to just "strSQL = strSQL & strSiteList"?
    Friday, August 25, 2017 6:58 PM
  • Hi,

    It depends on what is in strSiteList. For example, if the variable contains something like this:

    "Here","There","and Everywhere"

    then using In() would be fine. However, if the variable contains something more like:

    "Site='Here' OR Site='There' OR Site='and Everywhere'

    then using In() would not be appropriate and simply using the variable as-is might be good enough.

    Hope it helps...

    Friday, August 25, 2017 7:12 PM
  • Thx DB, I actually solved that on my own, believe it or not!  I changed it to match the structure already established:

    StrSQL = StrSQL & "([Site] = """ & strSiteList & """) AND "

    Now I'm trying to figure out how I do a wildcard search where the user can put anything they want in the txtWildcardSearch text box and all table fields will be searched for that text string, resulting in any records that have fields that include it.  Everything I've found as examples so far seems to include typing out the parameter to every single field.  I'm hoping there's a different way that could include something similar to "For every field...in [sfrmCustomSearchQuery] ...search for....Like "me.txtWildCardSearch"....  or something.  Yet to come up with something successful, though.

    Friday, August 25, 2017 8:05 PM
  • Hi,

    One approach to search "all" fields is to concatenate the values in all the fields and then use the wildcard character or the InStr() function to search the entered parameter or keyword in the record. For example, if the user enters "help" as a search term, and you want to search five fields, it might look something like this:

    WHERE [Field1] & [Field2] & [Field3] & [Field4] & [Field5] Like "'*" & strSearch & "*'"

    Hope it helps...

    • Marked as answer by R'C Wednesday, August 30, 2017 3:34 PM
    Friday, August 25, 2017 8:13 PM
  • OK, but where does the InStr() function come into play?
    Friday, August 25, 2017 8:15 PM
  • OK, but where does the InStr() function come into play?

    Hi,

    The example I gave you uses Like and wildcards, it doesn't need InStr(). If you want to use InStr(), then you don't use Like, and it might look something like this:

    WHERE InStr([Field1] & [Field2] & [Field3] & [Field4] & [Field5], strSearch) > 0

    Hope it helps...

    Friday, August 25, 2017 8:29 PM
  • Whether you call the Instr function as an alternative to the LIKE operator or not, to avoid specious substring matches insert a high ASCII character such as the tilde,~, between each column's value when you concatenate them.  This will avoid specious substring matches where a substring at the end of one value, combined with a substring at the start of the next, matches the substring you enter as the parameter.

    Before you create such a search facility, however, think carefully about whether it would serve any useful purpose.  If I'm entering a substring which I think is a part of a company name for instance,  I don't need to be looking in a City or County column.  In 30 years of modelling environmental data I've never had occasion to include this sort of functionality.  Where it is used, it is more often than not a result of a badly designed table in which data is encoded as column headings, in violation of the Information Rule.

    Ken Sheridan, Stafford, England

    Friday, August 25, 2017 8:35 PM
  • StrSQL = StrSQL & "([Site] = """ & strSiteList & """) AND "
    How would that work?  If the site list is "London,Paris,Rome", then Site = "London,Paris,Rome" will not be TRUE.  The expression would need to be

    Site IN("London","Paris","Rome")

    or:

    Site = "London" OR Site = "Paris", OR Site = "Rome"


    Ken Sheridan, Stafford, England

    Friday, August 25, 2017 8:42 PM
  • Well, that may be true, but part of the thought behind this search form design is that I'm covering my bases, so to speak.  I've had too many instances where I accomplish what has been asked only to be met with a "But why doesn't it do..."  So, even though this form has all these different search inputs, I honestly don't think it'll be what I ultimately present.  In case something "further" is requested of the search, I'll have the capability to provide it.  I also may use these searches in different locations.  I hope that makes some sense....

    Also, I got the wildcard search to work, but the string is huge and everywhere I tried to break it between the fields to the next line wasn't allowed so I've got this really long line of code as such....

    If Not IsNull(Me.txtWildCardSearch) Then
            StrSQL = StrSQL & "([IndexID] & [RecordID] & [StudyID] & [DateAdded] & [Company] & [ProjectManager] & [Site] & [Location] & [MaintenanceItem] & [Division] & [Description] & [Component] & [ComponentLocation] & [Manufacturer] & [InstallationDate] & [Age] & [Lifespan] & [RemainingLife] & [Quantity] & [Units] & [Cost] & [SubTotalCost] & [DesignerFee] & [ProjectManagementFee] & [TotalCost] Like ""*" & Me.txtWildCardSearch & "*"") AND "
        End If

    Friday, August 25, 2017 8:46 PM
  • Hi,

    To break up a line of code in VBA, you can use the continuation characters " _" (a space followed by the underscore). If it doesn't work, you can break down the variable assignment by appending the new value to itself. For example:

    var = var & 1
    var = var & 2
    var = var & 3
    and so on...

    Hope it helps...

    • Marked as answer by R'C Wednesday, August 30, 2017 3:34 PM
    Friday, August 25, 2017 8:49 PM
  • OK, I was wrong.  Or I got mixed up, I think.  I don't think that the "Site In("strSiteList")" code is going to work with the structure I've got.  So right now, it'll select a single site (I thought I'd gotten it to multi-select, but was wrong).  When I use the "In("strSiteList" as you had, Ken, it would filter to a blank subform.  I think that's because the code at this point is applying the string parameter to the subform filter.  I'm not sure if that's good, better, bad, but that's what it's doing I believe.
    Friday, August 25, 2017 8:57 PM
  • I'm hoping there's a different way that could include something similar to "For every field...in [sfrmCustomSearchQuery] ...search for....Like "me.txtWildCardSearch"....  or something.  Yet to come up with something successful, though.

    Hi R'C,

    In my approach this would not be difficult. When looping through all the fields, for each field a condition with LIKE would be build in in the where_string. It is a different search though then the one where you can give each field its own value to test for.

    But in all my applications (about 100) I have never had the question to add this functionality. In my opinion it has its use for an unstructured search in all data, but I assume that most data in the fields is structured. For a couple of fields it could be applicable, but a selection using a "joker" (wildcard) is already build in.

    Nevertheless, because adding new criteria or alternative search instructions are easy to implement, I will add this as an alternative option.

    In my approach I use a table to store the fieldnames. This makes the search program itself independant of which recordset/form is used, and because of the looping, you only need to program the typicalities of the different fieldtypes.

    Imb.

    Friday, August 25, 2017 9:28 PM
  • When I use the "In("strSiteList" as you had, Ken..............
    That's not what I had.  If you look at the code in my earlier example again you'll see that it concatenates the value of one variable (the value list) in to a string expression and assigns it to another variable (the criteria')

        strCriteria = "EmployeeID In(" & strEmployeeIDList & ")"

    In this the value of strEmployeeIDList could be something like 32,44,61,63,92.  the string expression assigned to the strCriteria variable would thus be:

       EmployeeID In(32,44,61,63,92)

    Note that the values here are the values of the numeric keys in the hidden column of the list box to which its BoundColumn property points.  Consequently the value list does not require each value to be wrapped in quotes as it would be if the values were text values.  In your case, if the values are the actual site names, rather than numeric keys as is more normally the case in a situation like this, each value would need to be wrapped in quotes characters.  This means that when building the value list, at each iteration of the loop through the control's ItemsSelected collection, each value must be wrapped in literal quotes characters.  A literal quotes character in a string is represented by a contiguous pair of quotes characters, so if we assume that in my example EmployeeID is of text data type rather than a number, the code within the loop would be:

        strEmployeeIDList = strEmployeeIDList & ",""" & ctrl.ItemData(varItem) & """"

    The above expression would then evaluate to:

       EmployeeID In("32","44","61","63","92")

    Whether the string expression is being used to filter a form or report, or to build an SQL statement which is assigned to a form or report's RecordSource property so that a restricted recordset is loaded into the form, is immaterial.  The methodology to build the string expression is the same in each case.

    As regards your 'search all columns' facility I agree entirely with Imb that it would be better to loop through the set of columns and build an expression in which Boolean OR operations are used to examine each column for the parameter value, whether on the basis of equality of the column's value with the parameter value (which is far more efficient as it allows use of the indexes) or, by pattern matching with the LIKE operator and the asterisk wildcard character.  The latter should be restricted as far as possible, however, because of the inherent inefficiency which the inability to use the indexes produces.


    Ken Sheridan, Stafford, England

    Friday, August 25, 2017 10:26 PM
  • I meant it as shorthand.  I had it as you presented.
    Friday, August 25, 2017 10:45 PM
  • Hello,

    Have your issue been resolved? i would suggest you mark helpful post to close this thread if your issue has been resolved. If you have any new or different issues, please post a new thread.

    If your issue is complicated, you may split it into serveral ones.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 28, 2017 10:03 AM
    Moderator
  • No, it's not solved yet.  Thank you.
    Monday, August 28, 2017 1:38 PM
  • OK, so after taking a few breaths and re-reading this topic a couple times, I've decided to reorganize my goal with this.  I was attacking this head on with the intent of trying to solve all the issues at one time, make all the controls optional yet compatible.  I need to divide and conquer instead.  So as I've mentioned previously I'm going to make the big goal into smaller goals before attempting to put it all together.

    So I've divided my controls up into their own forms to tackle.  I have 2 of those goals met (a cascading combo box search and a cascading list box search forms).  What remains are 3 search forms: wildcard search, range search, and option group search.

    Right now I'm tackling the wildcard search form.  I need help in getting the SQL string correctly formed and passing it onto the subform.  I'm not going to use the form filter for this.  I don't know if I care for that approach, honestly.  So, instead of having 1 text box to search all fields, I've broke this down into 5 categories with a text box each.  The first text box is pretty much for admin users (like me) who will have omnipotent access.  The other 4 will be for any user.  The categories (and groups) are thus:

    strGRP1: Database Data

    strGRP2: Study Data

    strGRP3: Component Data

    strGRP4: Forecast Data (this is for component install date, life cycle, etc.,--stuff we use in forecasting)

    strGRP5: Cost Data

    Presently, I'm just working on the first text box group.  I figure since I'm not going to have the option of combining the 5 wildcard searches (e.g., user could search by putting a value in each text box then search--I think it's better to have it so just 1 text box at a time creates the search), I can simply copy and paste the code once I've figured out just the first text box search.  I've updated my example database to reflect these new strategies and renamed it Sample3.  It can be accessed at either of these links (I hope---you should be able to edit it and upload the edit back into the folder but previous attempts didn't work for some reason):

    https://1drv.ms/u/s!Akvvcs6GUyjrhh1E3kEsZ247cKXr

    https://1drv.ms/f/s!Akvvcs6GUyjrbPFRH-9L8nEUNQw

    So I've went through the code and tried to remove what I believe was not needed and added what I believe is required.  Here's my code for the frmWildCardSearch form:

    Option Compare Database
    Option Explicit
    ---------------------------------------------------------------------
    Private Sub txtGrp1_AfterUpdate()
        Dim StrSQL As String                  'The criteria string.
        Dim lngLen As Long                      'Length of the criteria string to append to.
        Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
        Dim strGRP1 As String
        Dim strGRP2 As String
        Dim strGRP3 As String
        Dim strGRP4 As String
        Dim strGRP5 As String
            
    strGRP1 = "([IndexID] OR [RecordID] OR [StudyID] OR [DateAdded]"
    strGRP2 = "([Company] & [ProjectManager] & [Site] & [MaintenanceItem]"
    strGRP3 = "([Location] &  & [Division] & [Description] & [Component] & [ComponentLocation] & [Manufacturer]"
    strGRP4 = "([InstallationDate] & [Age] & [Lifespan] & [RemainingLife]"
    strGRP5 = "([Quantity] & [Units] & [Cost] & [SubTotalCost] & [DesignerFee] & [ProjectManagementFee] & [TotalCost]"
    StrSQL = "SELECT IndexID from tblIndex WHERE "

      If Not IsNull(Me.txtGrp1) Then
            StrSQL = StrSQL & strGRP1 & " Like ""*" & Me.txtGrp1 & "*"")"
        End If
       
        Me.txtIndex = StrSQL
        Me.sfrmCustomSearchQuery.Requery
        Debug.Print StrSQL
    End Sub

    ---------------------------------------------------------------------

     So I have the code printing the SQL string to the immediate window as well as a text box on the form (txtIndex) to see what string is being passed to the query:

    SELECT IndexID from tblIndex WHERE ([IndexID] OR [RecordID] OR [StudyID] OR [DateAdded] Like "*3*")

    I'm still not good with figuring out the correct structure of SQL strings, but I think this one is obviously incorrect.  I must say I'm sorry for how long this topic has gotten (as well as this post).  I certainly didn't mean for that.  Once this wildcard search form is solved I will post a new topic for help with anything else related to this.

    Monday, August 28, 2017 4:11 PM
  • SELECT IndexID from tblIndex WHERE ([IndexID] OR [RecordID] OR [StudyID] OR [DateAdded] Like "*3*")

    Hi R'C,

    The syntax should be:

        "SELECT IndexID from tblIndex WHERE ([IndexID] Like '*3*') OR (RecordID] Like '*3*') OR ([StudyID] Like '*3*') OR ([DateAdded] Like '*3*')"

    Are IndexID, RecordID, StudyID strings or longs?

    You can prepare a variable string:

        "SELECT IndexID from tblIndex WHERE ([IndexID] Like '*[]*') OR (RecordID] Like '*[]*') OR ([StudyID] Like '*[]*') OR ([DateAdded] Like '*[]*')"

    where you replace all "[]" by the Me.txtGrp1, and ready is your sql-string.

    Imb.

    Monday, August 28, 2017 6:21 PM
  • Hi lmb,

    Thank you for replying.  IndexID, RecordID, & StudyID are PKs so they're long.  I didn't think that would matter when doing a wildcard search?  I've been working on it and it seems to be working with the string as 

    SELECT IndexID from tblIndex WHERE ([IndexID] OR [RecordID] OR [StudyID] OR [DateAdded] Like "*X*")

    I've tried it with different selections to match values in each field and with values matching across various combinations of fields.  I'm still testing it, though.  Why would my string (seemingly) work if the syntax is incorrect?

    Monday, August 28, 2017 6:29 PM
  • Hi lmb,

    Thank you for replying.  IndexID, RecordID, & StudyID are PKs so they're long.  I didn't think that would matter when doing a wildcard search?  I've been working on it and it seems to be working with the string as 

    SELECT IndexID from tblIndex WHERE ([IndexID] OR [RecordID] OR [StudyID] OR [DateAdded] Like "*X*")

    Hi R'C,

    I must admit that I have never used a construnction like above. What does "it seems to be working with the string" mean? That you get no errors, or that you get the desired result?

    Imb.

    Monday, August 28, 2017 6:56 PM
  • I just mean that I can't find a way it doesn't work.

    That being said...I can't figure out how to make it work for all 5 text boxes without creating 5 different queries, maybe even 5 different forms.  The query it's based on includes criteria in the appropriate field for all the fields in a group.  So, for example, StudyID is in group 1 so in the base query, I have "LIKE "*" & [forms]![frmWildcardsearch]![txtgrp1] & "*"" This appears in each of the fields as an OR criteria.  Because of how many fields exist in the table, there are not enough criteria lines in Design view to incorporate that for each field.  I can write it out in SQL view, however, which I did, but it's not working for the 2nd text field...

    Monday, August 28, 2017 7:08 PM
  • Here's my query:

    SELECT tblIndex.IndexID, tblStudyRecords.RecordID, tblStudies.StudyID, tblStudies.DateAdded, tblCompanies.Company, tblProjectManagers.ProjectManager, tblSites.Site, tblStudyRecords.Location, tblStudyRecords.MaintenanceItem, tblStudyRecords.Division, tblStudyRecords.Description, tblStudyRecords.Component, tblStudyRecords.ComponentLocation, tblStudyRecords.Manufacturer, tblStudyRecords.InstallationDate, tblStudyRecords.Age, tblStudyRecords.Lifespan, tblStudyRecords.RemainingLife, tblStudyRecords.Quantity, tblStudyRecords.Units, tblIndex.Cost, tblIndex.SubTotalCost, tblStudies.DesignerFee, tblStudies.ProjectManagementFee, tblIndex.TotalCost
    FROM (tblSites INNER JOIN (tblProjectManagers INNER JOIN (tblCompanies INNER JOIN (tblStudies INNER JOIN tblIndex ON tblStudies.StudyID = tblIndex.StudyID) ON tblCompanies.CompanyID = tblStudies.Company) ON tblProjectManagers.ProjectManagerID = tblStudies.ProjectManager) ON (tblSites.SiteID = tblIndex.SiteID) AND (tblSites.SiteID = tblStudies.Site)) INNER JOIN tblStudyRecords ON (tblStudies.StudyID = tblStudyRecords.StudyID) AND (tblStudyRecords.RecordID = tblIndex.RecordID)
    WHERE (((tblIndex.IndexID) Like "*" & Forms!frmWildCardSearch!txtGrp1 & "*")) Or (((tblStudyRecords.RecordID) Like "*" & Forms!frmWildCardSearch!txtGrp1 & "*")) Or (((tblStudies.StudyID) Like "*" & Forms!frmWildCardSearch!txtGrp1 & "*")) Or (((tblStudies.DateAdded) Like "*" & Forms!frmWildCardSearch!txtGrp1 & "*"));

    And here's the code I'm presently trying.  I have to rename the Sub because I'm trying out code applicable to 5 fields and it currently resides in a single field after update Sub.

    Private Sub txtGrp1_AfterUpdate()
        Dim StrSQL As String                  'The criteria string.
        Dim strGRP1 As String
        Dim strGRP2 As String
        Dim strGRP3 As String
        Dim strGRP4 As String
        Dim strGRP5 As String
            
    strGRP1 = "([IndexID] OR [RecordID] OR [StudyID] OR [DateAdded]"
    strGRP2 = "([Company] & [ProjectManager] & [Site] & [MaintenanceItem]"
    strGRP3 = "([Location] &  & [Division] & [Description] & [Component] & [ComponentLocation] & [Manufacturer]"
    strGRP4 = "([InstallationDate] & [Age] & [Lifespan] & [RemainingLife]"
    strGRP5 = "([Quantity] & [Units] & [Cost] & [SubTotalCost] & [DesignerFee] & [ProjectManagementFee] & [TotalCost]"
    StrSQL = "SELECT IndexID from tblIndex WHERE "

      If Not IsNull(Me.txtGrp1) Then
            StrSQL = StrSQL & strGRP1 & " Like ""*" & Me.txtGrp1 & "*"")"
      End If
     
     If Not IsNull(Me.txtGrp2) Then
            StrSQL = StrSQL & strGRP2 & " Like ""*" & Me.txtGrp2 & "*"")"
        End If

        If Not IsNull(Me.txtGrp3) Then
            StrSQL = StrSQL & strGRP3 & " Like ""*" & Me.txtGrp3 & "*"")"
        End If
     
     If Not IsNull(Me.txtGrp4) Then
            StrSQL = StrSQL & strGRP4 & " Like ""*" & Me.txtGrp4 & "*"")"
        End If
        
    If Not IsNull(Me.txtGrp5) Then
            StrSQL = StrSQL & strGRP5 & " Like ""*" & Me.txtGrp5 & "*"")"
        End If
        
     If DCount([IndexID], "qryFrmWildCardQuery") = 0 Then
     MsgBox "No Matching Criteria", vbOKOnly, "No Results"
     End If
     
        Me.txtIndex = StrSQL
        Me.sfrmCustomSearchQuery.Requery
        Debug.Print StrSQL
    End Sub

    Monday, August 28, 2017 9:07 PM
  • SELECT IndexID from tblIndex WHERE ([IndexID] OR [RecordID] OR [StudyID] OR [DateAdded] Like "*X*")
    That does not do what you think.  It does not compare each of the columns with "*X*.  What it does is apply the Boolean OR operations to the values of IndexID, RecordID and StudyID and the to the result of the expression DateAdded LIKE "*X*.  As any non-zero value evaluates to TRUE the WHERE clause will evaluate to TRUE as a whole provided that any one of IndexID, RecordID and StudyID contain a non-zero value.  Assuming that this will be the case in each row, the result of the expression DateAdded LIKE "*X* is immaterial.

    Ken Sheridan, Stafford, England

    Monday, August 28, 2017 10:36 PM
  • Thank you for explaining that, Ken.  I've changed my code to reflect this.  Group 1 text box will work showing results but my text box 2 isn't for some reason.  Here is the syntax I have, and maybe I messed up something here, too, so please feel free to point it out...

    SELECT IndexID from tblIndex WHERE (([Company] Like "*X*") OR ([ProjectManager] Like "*X*") OR ([Site] Like "*X*") OR ([MaintenanceItem] Like "*X*"))

    Could it be something to do with the table the string is based on?  

    Tuesday, August 29, 2017 1:32 PM
  • I am finding this to be very difficult.  I can get the correct results using text box 1/group 1, but no others.  Not with the way the code stands.  I can get the correct results if I run text box 2 with a different query, but not the same query as text box 1.  Yet, I cannot combine the queries because that would allow a wildcard search from 1 text box for all the table fields.

    So can anyone help me here?  I know there's been plenty help previously and I'm pushing it I think, but not sure where else to look.  I previously had the text box searching every field (successfully, I thought) but after some of your advice I changed it and have become stuck. I've looked through online examples and through my books.  It seems like I need to be able to indicate that with each text box there are different parameters--like a different query.  I don't think you can have a subform that is built from multiple queries.  

    This is the query for text box 1 that works:

    SELECT tblIndex.IndexID, tblStudyRecords.RecordID, tblStudies.StudyID, tblStudies.DateAdded, tblCompanies.Company, tblProjectManagers.ProjectManager, tblSites.Site, tblStudyRecords.Location, tblStudyRecords.MaintenanceItem, tblStudyRecords.Division, tblStudyRecords.Description, tblStudyRecords.Component, tblStudyRecords.ComponentLocation, tblStudyRecords.Manufacturer, tblStudyRecords.InstallationDate, tblStudyRecords.Age, tblStudyRecords.Lifespan, tblStudyRecords.RemainingLife, tblStudyRecords.Quantity, tblStudyRecords.Units, tblIndex.Cost, tblIndex.SubTotalCost, tblStudies.DesignerFee, tblStudies.ProjectManagementFee, tblIndex.TotalCost
    FROM (tblSites INNER JOIN (tblProjectManagers INNER JOIN (tblCompanies INNER JOIN (tblStudies INNER JOIN tblIndex ON tblStudies.StudyID = tblIndex.StudyID) ON tblCompanies.CompanyID = tblStudies.Company) ON tblProjectManagers.ProjectManagerID = tblStudies.ProjectManager) ON (tblSites.SiteID = tblStudies.Site) AND (tblSites.SiteID = tblIndex.SiteID)) INNER JOIN tblStudyRecords ON (tblStudyRecords.RecordID = tblIndex.RecordID) AND (tblStudies.StudyID = tblStudyRecords.StudyID)
    WHERE (((tblIndex.IndexID) Like "*" & Forms!frmWildCardSearch!txtGrp1 & "*")) Or (((tblStudyRecords.RecordID) Like "*" & Forms!frmWildCardSearch!txtGrp1 & "*")) Or (((tblStudies.StudyID) Like "*" & Forms!frmWildCardSearch!txtGrp1 & "*")) Or (((tblStudies.DateAdded) Like "*" & Forms!frmWildCardSearch!txtGrp1 & "*"));

    This is the After Update code for text box 1:

    Private Sub txtGrp1_AfterUpdate()
        
        Dim StrSQL As String
        Dim strGRP1 As String
                
    strGRP1 = "(([IndexID] Like ""*" & Me.txtGrp1 & "*"") OR ([RecordID] Like ""*" & Me.txtGrp1 & "*"") OR ([StudyID] Like ""*" & Me.txtGrp1 & "*"") OR ([DateAdded] Like ""*" & Me.txtGrp1 & "*""))"
    
    StrSQL = "SELECT IndexID from tblIndex WHERE "
    
      If Not IsNull(Me.txtGrp1) Then
            StrSQL = StrSQL & strGRP1 ' & " Like ""*" & Me.txtGrp1 & "*"")"
      End If
     
      If DCount([IndexID], "qryFrmWildCardQuery") = 0 Then
     MsgBox "No Matching Criteria", vbOKOnly, "No Results"
     End If
     
        Me.txtIndex = StrSQL
        Me.sfrmCustomSearchQuery.Requery
        Debug.Print StrSQL
    End Sub

    This is the SQL string text box 1 builds from that:

    SELECT IndexID from tblIndex WHERE (([IndexID] Like "*X*") OR ([RecordID] Like "*X*") OR ([StudyID] Like "*X*") OR ([DateAdded] Like "*X*"))

    For text box 2, this query will work when I put a character into the txtGrp2 text box and then open a different query (I tried pasting the SQL into the code as it is in text box 1 after update event, but it wouldn't work and I can't figure out why):

    SELECT tblIndex.IndexID, tblCompanies.Company, tblProjectManagers.ProjectManager, tblSites.Site, tblStudyRecords.MaintenanceItem
    FROM (tblSites INNER JOIN (tblProjectManagers INNER JOIN (tblCompanies INNER JOIN (tblIndex INNER JOIN tblStudies ON tblIndex.StudyID = tblStudies.StudyID) ON tblCompanies.CompanyID = tblStudies.Company) ON tblProjectManagers.ProjectManagerID = tblStudies.ProjectManager) ON (tblSites.SiteID = tblIndex.SiteID) AND (tblSites.SiteID = tblStudies.Site)) INNER JOIN tblStudyRecords ON (tblStudies.StudyID = tblStudyRecords.StudyID) AND (tblStudyRecords.RecordID = tblIndex.RecordID)
    WHERE (((tblCompanies.Company) Like "*" & Forms!frmWildCardSearch!txtGrp2 & "*")) Or (((tblProjectManagers.ProjectManager) Like "*" & Forms!frmWildCardSearch!txtGrp2 & "*")) Or (((tblSites.Site) Like "*" & Forms!frmWildCardSearch!txtGrp2 & "*")) Or (((tblStudyRecords.MaintenanceItem) Like "*" & Forms!frmWildCardSearch!txtGrp2 & "*"));

    This is the After Update event for text box 2:

    Private Sub txtGrp2_AfterUpdate()
      Dim StrSQL As String
        
        Dim strGRP2 As String
       
    
    strGRP2 = "(([Company] Like ""*" & Me.txtGrp2 & "*"") OR ([ProjectManager] Like ""*" & Me.txtGrp2 & "*"") OR ([Site] Like ""*" & Me.txtGrp2 & "*"") OR ([MaintenanceItem] Like ""*" & Me.txtGrp2 & "*""))"
    
    StrSQL = "SELECT IndexID from tblIndex WHERE "
    
      
     If Not IsNull(Me.txtGrp2) Then
            StrSQL = StrSQL & strGRP2 & ";" ' & " Like ""*" & Me.txtGrp2 & "*"")"
        End If
    
    If DCount([IndexID], "qryFrmWildCardQuery") = 0 Then
     MsgBox "No Matching Criteria", vbOKOnly, "No Results"
     End If
     
        Me.txtIndex = StrSQL
        Me.sfrmCustomSearchQuery.Requery
        Debug.Print StrSQL
    
    End Sub

    This is the SQL string text box 2/txtGrp2 builds:

    SELECT IndexID from tblIndex WHERE (([Company] Like "*X*") OR ([ProjectManager] Like "*X*") OR ([Site] Like "*X*") OR ([MaintenanceItem] Like "*X*"));

    Again, please help...I know I've been too long on this topic, but I can't figure out what is wrong (or in some cases what is wrong that appears to work right) and how to correct it.

    R'C

    • Edited by R'C Tuesday, August 29, 2017 3:32 PM incorrectly pasted text
    Tuesday, August 29, 2017 3:31 PM
  • Thank you for explaining that, Ken.  I've changed my code to reflect this.  Group 1 text box will work showing results but my text box 2 isn't for some reason.  Here is the syntax I have, and maybe I messed up something here, too, so please feel free to point it out...

    SELECT IndexID from tblIndex WHERE (([Company] Like "*X*") OR ([ProjectManager] Like "*X*") OR ([Site] Like "*X*") OR ([MaintenanceItem] Like "*X*"))

    Could it be something to do with the table the string is based on?  

    Provided that the columns are all of text data type, then the SQL statement per se looks fine, tough you don't actually need to parenthesise each LIKE operation.

    Where I'm experiencing difficulty in knowing how to advise you is that I cannot see the wood for the trees.  I'm not even sure what basis you are using to restrict the result set returned.  On the one hand your query is referencing controls as parameters, while at the same time you are building string expressions in code, but without assigning the resulting string expression either to a form's RecordSource property, or to its Filter property, or to the WhereCondition argument of the OpenForm or OpenReport method. when opening a form or report.

    While referencing controls as parameters works well for structured searches, and is in most cases my favoured approach due to the simplicity of the logic in the query's WHERE clause, I don't think it's a good solution for an unstructured search interface of the type you are aiming for.  As I've said before I question the usefulness of this sort of interface, but on the basis that you wish to go ahead with it, I would recommend you forget about parameters in a query, and limit the methodology to building a string expression which can then be assigned to the RecordSource property of a form or report.  For this I would start small and test the routine as you build on it.  Don't be too ambitious to star with; keep to simple criteria, and build up the complexity as you develop the routine.  If it fails, you'll then know which addition to the routine caused the failure and be able to debug it.

    The following is an example from one of my demo files which does this when opening a report from a dialogue form, though it could, with very little modification open a form, or assign the string expression to the RecordSource property of the form in whose module the code is executed:

    Private Sub cmdOpenReport_Click()

        Dim strSQL As String
        Dim strWhere As String
        
        strSQL = "SELECT Employees.*, FirstName & "" "" & Lastname AS FullName, " & _
            "Projects.Project FROM Projects " & _
            "RIGHT JOIN (Employees LEFT JOIN ProjectEmployees " & _
            "ON Employees.EmployeeID=ProjectEmployees.EmployeeID) ON " & _
            "Projects.ProjectID=ProjectEmployees.ProjectID WHERE True"
        
        If Not IsNull(Me.cboDepartment) Then
            strWhere = strWhere & " And Department = """ & Me.cboDepartment & """"
        End If
        
        If Not IsNull(Me.cboCity) Then
            strWhere = strWhere & " And City = """ & Me.cboCity & """"
        End If
            
        If Not IsNull(Me.txtDateFrom) Then
            strWhere = strWhere & " And DateAppointed >= #" & _
                Format(Me.txtDateFrom, "yyyy-mm-dd") & "#"
        End If
       
        If Not IsNull(Me.txDateTo) Then
            strWhere = strWhere & " And DateAppointed < #" & _
                Format(Me.txDateTo, "yyyy-mm-dd") & "#+1"
        End If
        
        strSQL = strSQL & strWhere & ";"
        
        DoCmd.OpenReport "rptEmployees", View:=acViewPreview, OpenArgs:=strSQL

    End Sub

    You'll see that this code starts by building the basic SQL statemen and assigning it to the strWHERE variable.  Note that its WHERE clause is simply:

        WHERE True

    This will return all rows of course.  By including this otherwise useless WHERE clause in the basic SQL statement, it then becomes possible to add further criteria to it.  This is done by each If….End If construct in the following code which concatenates a further expression to the strWHERE variable.  In my case these all test for equality, but you could easily test for pattern match with the LIKE operator in the constructs.

    In each case the  Boolean AND operator is used, which requires matches in all of the columns for which a value has been entered in the dialogue form.  Boolean OR operators could be used instead of course, which would require a match in only one of the columns.  If you want to mix AND and OR operators it gets a little trickier as you then have to ensure that parentheses are used in the correct.   In your example where you want to want to look for the same value in multiple columns, this would mean using OR operations, as you've done in the quoted example above.  These OR operations would be built within an If….End If construct, and parenthesised so that they are evaluated independently of the AND operations between the criteria built by each If….End If construct.

    If you follow a consistent methodology like this, and progress methodically, building up the routine from simple beginnings and testing it at each stage, fixing any errors in the syntax or the logic as you encounter them, you should eventually end up with a flexible interface of the type you are attempting to build.


    Ken Sheridan, Stafford, England

    Tuesday, August 29, 2017 6:09 PM
  • Thank you Ken.  I understand I've not been explaining clearly.  I've struggled with that my entire life, honestly.  So this wildcard search will be a part of a database that will have thousands of records (eventually) and there are just a handful of people that will use it (in the beginning) and will, at times, not be able to find a record because they are looking for a value that is written differently than what the user will expect, however, they may know something about a different field in the record and will need a way to search for it.  It may be a date, it may be three letters, etc., etc.  Initially, my idea was to search any field through 1 text box, but after considering your advice about restricting that search I decided to break it down into groups.  I hope to then provide it as an option group that will allow the user to choose which group of fields to search and then enter the wildcard characters for that search.

    I can rephrase all that easily by just saying that this was what I was told to do it by the boss!  Most of the problems I encounter are because I present something that works in the database and then I'm asked something along the lines of "How do I get it to show me a record if I only know the lifespan?" or "But what if I only know how many of something I have?"  So then I have to go back and come up with something that will provide that capability.  In this case, I was tasked with several different search methods, this being one.

     So, I think I may have gotten this working without having to use much code.  It relies mostly on the query and then just a requery command in the after update event, pretty much.  This is my query SQL now:

    SELECT tblIndex.IndexID, tblStudyRecords.RecordID, tblStudies.StudyID, tblStudies.DateAdded, tblCompanies.Company, tblProjectManagers.ProjectManager, tblSites.Site, tblStudyRecords.Location, tblStudyRecords.MaintenanceItem, tblStudyRecords.Division, tblStudyRecords.Description, tblStudyRecords.Component, tblStudyRecords.ComponentLocation, tblStudyRecords.Manufacturer, tblStudyRecords.InstallationDate, tblStudyRecords.Age, tblStudyRecords.Lifespan, tblStudyRecords.RemainingLife, tblStudyRecords.Quantity, tblStudyRecords.Units, tblIndex.Cost, tblIndex.SubTotalCost, tblStudies.DesignerFee, tblStudies.ProjectManagementFee, tblIndex.TotalCost
    FROM (tblSites INNER JOIN (tblProjectManagers INNER JOIN (tblCompanies INNER JOIN (tblStudies INNER JOIN tblIndex ON tblStudies.StudyID = tblIndex.StudyID) ON tblCompanies.CompanyID = tblStudies.Company) ON tblProjectManagers.ProjectManagerID = tblStudies.ProjectManager) ON (tblSites.SiteID = tblIndex.SiteID) AND (tblSites.SiteID = tblStudies.Site)) INNER JOIN tblStudyRecords ON (tblStudies.StudyID = tblStudyRecords.StudyID) AND (tblStudyRecords.RecordID = tblIndex.RecordID)
    WHERE (((tblIndex.IndexID & tblStudyRecords.RecordID & tblStudies.StudyID & tblStudies.DateAdded) Like "*" & Forms!frmWildCardSearch2!txtGrp1 & "*") And ((tblCompanies.Company & tblProjectManagers.ProjectManager & tblSites.Site & tblStudyRecords.MaintenanceItem) Like "*" & Forms!frmWildCardSearch2!txtGrp2 & "*") And (([Location] & [Division] & [Description] & [Component] & [ComponentLocation] & [Manufacturer]) Like "*" & Forms!frmWildCardSearch2!txtGrp3 & "*") And (([InstallationDate] & [Age] & [Lifespan] & [RemainingLife]) Like "*" & Forms!frmWildCardSearch2!txtGrp4 & "*") And ((tblStudyRecords.Quantity & tblStudyRecords.Units & tblStudyRecords.Cost & tblStudyRecords.SubTotalCost & tblStudies.DesignerFee & tblStudies.ProjectManagementFee & tblIndex.TotalCost) Like "*" & Forms!frmWildCardSearch2!txtGrp5 & "*"))
    GROUP BY tblIndex.IndexID, tblStudyRecords.RecordID, tblStudies.StudyID, tblStudies.DateAdded, tblCompanies.Company, tblProjectManagers.ProjectManager, tblSites.Site, tblStudyRecords.Location, tblStudyRecords.MaintenanceItem, tblStudyRecords.Division, tblStudyRecords.Description, tblStudyRecords.Component, tblStudyRecords.ComponentLocation, tblStudyRecords.Manufacturer, tblStudyRecords.InstallationDate, tblStudyRecords.Age, tblStudyRecords.Lifespan, tblStudyRecords.RemainingLife, tblStudyRecords.Quantity, tblStudyRecords.Units, tblIndex.Cost, tblIndex.SubTotalCost, tblStudies.DesignerFee, tblStudies.ProjectManagementFee, tblIndex.TotalCost;
    

    Each text box has pretty much the same after update event, for example:

    Private Sub txtGrp1_AfterUpdate()
        
      If DCount([IndexID], "qryFrmWildCardQuery2") = 0 Then
     MsgBox "No Matching Criteria", vbOKOnly, "No Results"
     End If
      
        Me.sfrmWildCardQuery2.Requery
    
    End Sub
    Please let me know what you think...

    Tuesday, August 29, 2017 7:46 PM
  • The sort of questions you have cited as examples of those that users might ask suggests that they are aware of in which column a value, or partial value, might be.  In which case, to do this with a query I would not group the columns in the way you are doing as it could well return specious mismatches.  I would have a separate parameter for each column and make each optional by testing for NULL.

    The basis of this is that in the WHERE clause each parameter is tested in this way:

    WHERE (SomeColumn = [some parameter]
      OR [some parameter] IS NULL)
    AND (SomeOtherColumn = [some other parameter]
      OR [some other parameter] IS NULL)
    AND etc

    You can of course use:

        SomeColumn LIKE "*" [some parameter] & "*"

    where it is appropriate to use pattern matching rather than testing for equality.  Bear in mind, however, that even when applied to a single column this can still result in specious mismatches, though the likelihood is reduced in comparison with doing so on the concatenated values from a group of columns.  Moreover, the LIKE operator does not allow use of the indexes, so can reduce performance significantly.

    The logic behind this approach is extremely simple and consequently, given good indexing in the table, very efficient.  It also has the advantage of not having to bother about the data type of the column in question, so unlike when building an SQL statement in code, consideration does not need to be given as to whether the values need delimiting or not.  

    Each OR operation is enclosed in parentheses to force it to evaluate independently.  These parenthesized expressions are then tacked together with AND operations.  The way it works is that each parenthesized OR expression will evaluate to TRUE for each row where the value in the column is that of the parameter, or if the parameter is left empty (NULL),  for every row.  By virtue of the AND operations  the WHERE clause as a whole will evaluate to TRUE for those rows where all of the parenthesized expressions evaluate to TRUE, so those rows will be returned.

    Note that when you do this, parameters should only be declared if they are of DateTime data type.  If other types were declared they could never be Null.  DateTime parameters are unusual in this respect, and it's always advisable to declare them to avoid their being misinterpreted as arithmetical expressions rather than dates.

    When building a query like this, the basic unrestricted query can be built in query design view, but the WHERE clause should always be written and, most importantly, saved in SQL view.  This applies to the initial saving of the query, and if any subsequent amendments are made.  If it's saved in design view Access will move things around and at best the logic will be obscured, at worst it might become too complex to open.  It's a good idea to save the SQL of such queries in a text file in Notepad or similar, as if anything does go wrong you then have something to copy and paste back into the query designer in SQL view.

    Note BTW that if searching on the basis of a date range this can be made a closed range or open ended in either direction by treating the start and end date parameters independently, rather than within a BETWEEN….AND operation:

     WHERE (DateColumn >= [some parameter]
      OR [some parameter] IS NULL)
    AND (DateColumn < [some other parameter]+1
      OR [some other parameter] IS NULL)

    Beyond this I don't think I can contribute anything more of use to you, so I shall not be monitoring this thread any further.

    Ken Sheridan, Stafford, England

    • Marked as answer by R'C Wednesday, August 30, 2017 3:34 PM
    Tuesday, August 29, 2017 10:54 PM