none
Using combobox value within SQL WHERE statement RRS feed

  • Question

  • Hi there,

    I have created a table and form in Access 2013 with a number of fields including combo boxes in order to control data entry to a specific list. Now I want the user to be able to search through multiple fields to find specific information and I'm using just one text box to do this from. So I created a search string text box and a search button.

    I'm using VBA because my Where statement is very long. 

    Question 1: The VBA code works for short and long text field but not for values from combo boxes. Instead it returns a blank query. Is there something I need to do to identify that the field is a combo box within either the SELECT or WHERE component of the SQL statement? I have separate tables for the values of the combo boxes, a simple query that includes all fields, and one form.

    Question 2: I would like to send the results of the SQL query to a subform/table that resides within the ISS Work Tracking System form instead of to a separate query.

    FYI:

    • The table is called Data
    • The form is called ISS Work Tracking System
    • the text box to enter the search criteria is called txtSearchString

    Here is the code:

    Private Sub cmdSearch_Click()
    Dim SQLString As String
    Dim qdf As QueryDef

    SQLString = "SELECT Data.[Work item number], Data.Title, Data.[High level description], Data.[Detailed description], Data.Owner, Data.[Activity log], Data.[Activity date], Data.[User name], Data.[ISS category], Data.[Work type], Data.Source, Data.[Business process owner], Data.Status, Data.Priority, Data.[For immediate attention], Data.[For immediate attention priority], Data.[Expected release date], Data.[Jira prepared], Data.[Jira number], Data.[Jira prepared on], Data.[ISERT provided], Data.[e502 prepared], Data.[e502 number], Data.Field1, Data.Field2, Data.[Last modified by], Data.[Last modified date], Data.[Record creation date]" & _
        "FROM Data " & _
        "WHERE (((Data.Title) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.[High level description]) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.Owner) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.[Activity log]) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.[ISS Category]) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*""))  OR (((Data.[Work type]) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.Source) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.[Business process owner]) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.Status) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*""))" & _
        "OR (((Data.Priority) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.[Work item number]) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*""));"

    On Error Resume Next
    DoCmd.Close acQuery, "tempqry"
    DoCmd.DeleteObject acQuery, "tempQry"
    On Error GoTo 0
    Set qdf = CurrentDb.CreateQueryDef("tempQry", SQLString)
    DoCmd.OpenQuery ("tempQry")

    End Sub

    Any help would be greatly appreciated--I'm fairly new to this type of work in Access and have watched countless videos on uTube to get this far!

    Jill



    Tuesday, March 20, 2018 6:05 PM

All replies

  • Hi Jill,

    For Question #1, you seem to have fallen victim to The Evils of Lookup Field in Tables.

    It is highly discouraged to design tables with comboboxes in the fields. You might consider fixing this first before tackling your search problem.

    Just my 2 cents...

    Tuesday, March 20, 2018 6:58 PM
  • I would not recommend using a single text box control as a parameter.  Firstly, not all columns on which you wish to search are likely to be of text data type, so the problem of delimiting the values arises; number data types require no delimiters, date/time data types require the # character as the delimiters for instance, and the date literal's format must be mm/dd/yyyy or an otherwise internationally unambiguous format such as the ISO standard of YYYY-MM-DD.  Also there is the problem you have encountered that the value of a column whose DisplayControl property is a combo box will in most cases not be the value you see in the control, but a hidden numeric key value.  If you have used the 'lookup field' wizard when designing a table, this will be the case (one reason why most experienced Access developers recommend that this feature be avoided like the plague).

    In most work environments, it will be better to decide on which columns the users will need to search the database, and include an appropriate unbound control for each column in a search form.  The most appropriate type of unbound control will in most cases be a combo box, as this allows for searches on exact values only and avoids mismatches through typos on the users part.  Sometimes a multi-select list box will be used, where it is appropriate to search on multiple values of the same column.

    For a single interface which both allows the user to search a table or query, and display the results of the search, this can be done with a form of the sort illustrated as the 'drill down through a hierarchy' option in the demo file ComboDemo.zip in my public folder at:

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

    The form includes three unbound combo boxes in its header.  The RowSource of each is a UNION query which returns an 'All…." row as the first row in the list, e.g. For Counties:

    SELECT CountyID, County, 1 As SortColumn
    FROM Counties
    UNION
    SELECT  0, "All Counties", 1
    FROM Counties
    ORDER BY SortColumn, County;

     The fact that the data in this case is hierarchical is by-the-bye, however; the columns (fields) being searched could be independent of each other.

    The way it's done is to base the form on a query which references the three combo boxes as parameters:

    SELECT Counties.County, Districts.District, Parishes.Parish, Locations.Location
    FROM ((Counties INNER JOIN Districts ON Counties.CountyID=Districts.CountyID)
    INNER JOIN Parishes ON Districts.DistrictID=Parishes.DistrictID)
    INNER JOIN Locations ON Parishes.ParishID=Locations.ParishID
    WHERE (Counties.CountyID=Forms!frmDrillDown!cboGotoCounty
       OR Forms!frmDrillDown!cboGotoCounty = 0)
    AND (Districts.DistrictID=Forms!frmDrillDown!cboGotoDistrict
      OR Forms!frmDrillDown!cboGotoDistrict = 0)
    AND (Parishes.ParishID=Forms!frmDrillDown!cboGotoParish
      OR Forms!frmDrillDown!cboGotoParish = 0)
    ORDER BY Counties.County, Districts.District, Parishes.Parish

    The important thing here is that not only does the query test for the value in a column equalling that of the relevant control in the form, but also for the parameter (the control) being zero, i.e. The 'All…' option having been selected.  The county combo box for instance is referenced in the query's WHERE clause with:

    (Counties.CountyID=Forms!frmDrillDown!cboGotoCounty
    OR Forms!frmDrillDown!cboGotoCounty = 0)

    Note how the OR operation is parenthesized.  This is important as it forces the operation to evaluate independently of the AND operations.  The way it works is that if the value in a row equals that of the control in the form the first part of the OR operation will evaluate to True, so that row will be retuned; if the control = 0 then the second part of the OR operation will evaluate to True regardless of the value in the column, so all rows will be returned.

    By doing the same for each control in the form's header, and tacking the parenthesized OR operations together with AND operators, only those rows for which all three of the parenthesized OR operations evaluate to True will be returned.

    To cause the form to show the rows restricted on the basis of the selections in the combo boxes it is necessary to requery the form.  This is done in the AfterUpdate event procedure of each of the three combo boxes with the line:

    Me.Requery

    There is other code in the AfterUpdate event procedures, but that is only because of the hierarchical nature of the data and correlates the combo boxes.  Where there is no such hierarchical relationship you only need the single line to requery the form as above.

    BTW to view the form's RowSource query do so by zooming the RowSource property in from design view.  Don't use the build button (the one with 3 dots) to view the RowSource in query design view as Access will move things around and the logic of the query's WHERE clause will be obscured.  When writing a query like this, with a series of parenthesized OR operations, always do it in SQL view.  You can use the query designer to build the basic query without the WHERE clause, and then switch to SQL view to add the WHERE clause.

    The thing about this approach is that the logic is so simple, it's just a case of testing, in the case of each control, for:

    (SomeColumn = <some parameter>
    OR <some parameter>  = 0)

    As many parenthesized operations like this as necessary can be tacked together with the AND operator.  Given good table design, in particular good indexing of the columns in question, performance is generally very good in my experience.

    You'll find other examples of how to search a database by means of unbound controls in the section on 'Retrieving data from the database' in DatabaseBasics.zip in the OneDrive folder to which I gave you a link above.

    Ken Sheridan, Stafford, England

    Tuesday, March 20, 2018 7:05 PM