Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Unanswered: Building Multi-Field Search Form/Query where some fields are blank

    Hey all,

    I'm new to Access so please excuse me.

    I have created a new database that I wish people to be able to search to locate record holding (paper files, electronic files, maps, etc.).

    I want the end user to have a search form with many fields that they can use to keyword search. Example: In the Department field they could pick Legal then choose "Original" in the OriginalCopy field then maybe enter the dates of the file and be able to return results. Of course, they wouldn't have to enter a value in every field to return results and not every value in the datasheet would have a value... So I want the search form/query to search over null cells and every cell.

    I created a search form then a query and started building my criteria and this is what I have for the SQL code but it isn't working. I can put in a box number that I know is listed but it yields no results. Same for other fields as well.

    Can anyone direct me to what I'm missing?

    Thank you!

    Code:
    SELECT Inventory.Department, Inventory.OriginalCopy, Inventory.RecordsLoc, Inventory.PhysicalOther, Inventory.ElectronicLoc, Inventory.ElectronicOther, Inventory.LastNameMaintainer, Inventory.Description, Inventory.Status, Inventory.Restrictions, Inventory.RecordsMed, Inventory.MediumOther, Inventory.Arrangement, Inventory.ArrangementOther, Inventory.RecordsCond, Inventory.Storage, Inventory.StorageOther, Inventory.RetentionCurrent, Inventory.InclusiveDates, Inventory.LastNameInventory, Inventory.FileFormat, Inventory.SoftHardWare, Inventory.IMBox
          FROM Inventory
          WHERE (((Inventory.Department) Like "*" & [Forms]![AllQuery]![Department] & "*") OR 
                        ((Inventory.OriginalCopy) Like "*" & [Forms]![AllQuery]![OriginalCopy] & "*") OR 
                        ((Inventory.RecordsLoc) Like "*" & [Forms]![AllQuery]![RecordsLoc] & "*") OR 
                        ((Inventory.PhysicalOther) Like "*" & [Forms]![AllQuery]![PhysicalOther] & "*") OR 
                        ((Inventory.ElectronicLoc) Like "*" & [Forms]![AllQuery]![ElectronicLoc] & "*") OR 
                        ((Inventory.ElectronicOther) Like "*" & [Forms]![AllQuery]![ElectronicOther] & "*") OR 
                        ((Inventory.LastNameMaintainer) Like "*" & [Forms]![AllQuery]![LastNameMaintainer] & "*") OR 
                        ((Inventory.Description) Like "*" & [Forms]![AllQuery]![Description] & "*") OR 
                        ((Inventory.Status) Like "*" & [Forms]![AllQuery]![Status] & "*") OR 
                        ((Inventory.Restrictions) Like "*" & [Forms]![AllQuery]![Restrictions] & "*") OR 
                        ((Inventory.RecordsMed) Like "*" & [Forms]![AllQuery]![RecordsMed] & "*") OR 
                        ((Inventory.MediumOther) Like "*" & [Forms]![AllQuery]![MediumOther] & "*") OR 
                        ((Inventory.Arrangement) Like "*" & [Forms]![AllQuery]![Arrangement] & "*") OR 
                        ((Inventory.ArrangementOther) Like "*" & [Forms]![AllQuery]![ArrangementOther] & "*") OR 
                        ((Inventory.RecordsCond) Like "*" & [Forms]![AllQuery]![RecordsCond] & "*") OR 
                        ((Inventory.Storage) Like "*" & [Forms]![AllQuery]![Storage] & "*") OR 
                        ((Inventory.StorageOther) Like "*" & [Forms]![AllQuery]![StorageOther] & "*") OR 
                        ((Inventory.RetentionCurrent) Like "*" & [Forms]![AllQuery]![RetentionCurrent] & "*") OR 
                        ((Inventory.InclusiveDates) Like "*" & [Forms]![AllQuery]![InclusiveDates] & "*") OR 
                        ((Inventory.LastNameInventory) Like "*" & [Forms]![AllQuery]![LastNameInventory] & "*") OR 
                        ((Inventory.FileFormat) Like "*" & [Forms]![AllQuery]![FileFormat] & "*") OR 
                        ((Inventory.SoftHardWare) Like "*" & [Forms]![AllQuery]![SoftHardWare] & "*") OR 
                        ((Inventory.IMBox) Like "*" & [Forms]![AllQuery]![IMBox] & "*") OR 
                        ((Inventory.IMBarcode) Like "*" & [Forms]![AllQuery]![IMBarcode] & "*"));

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It such a situation it's generally better to dynamically build the SQL expression of the query (at least for the WHERE clause). That way, you only keep a list of columns for which the corresponding TextBox is used. This solution also offers better performances and is easier to debug and maintain.
    Have a nice day!

  3. #3
    Join Date
    Feb 2013
    Posts
    3

    Re: Building Multi-Field Search Form/Query where some fields are blank

    Hello,

    Thank you for responding but, again, I'm new to Access so do you mean use the Build Expression in Design view?

    Sorry for my ignorance!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by alemonlemon View Post
    ... do you mean use the Build Expression in Design view?
    Not exactly. I mean using a VBA procedure to assemble (build) a string variable containing the criteria (WHERE clause) of the query. You can scan each control on the form that can be used in the search and, if it's in use (i.e. its value is not null), add the corresponding subcriteria to the string. Ex. (air code):
    Code:
    Dim ctl As Control
    Dim strCriteria As String
    
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
            If Nz(ctl.Value, "") <> "" Then
                If len(strCriteria) > 0 Then strCriteria = strCriteria & " AND "
                strCriteria = strCriteria & ctl.Name & " Like '" & ctl.Value & "*'"
            End If
        End If
    Next ctl
    Note: In this example, I suppose that the name of each TextBox is the same as the name of its matching column in the table (or query) where the search is to be performed.
    Have a nice day!

  5. #5
    Join Date
    Feb 2013
    Posts
    3

    RE: Building Multi-Field Search Form/Query where some fields are blank

    Thank you for all your help. I really do appreciate it!

    But maybe this is beyond my scope?

    To answer, yes, all the names of each field is the same as the name of its matching column in query.

    I have the following SQL on the Query:

    Code:
    SELECT Inventory.Department, Inventory.OriginalCopy, Inventory.RecordsLoc, Inventory.PhysicalOther, Inventory.ElectronicLoc, Inventory.ElectronicOther, Inventory.LastNameMaintainer, Inventory.Description, Inventory.Status, Inventory.Restrictions, Inventory.RecordsMed, Inventory.MediumOther, Inventory.Arrangement, Inventory.ArrangementOther, Inventory.RecordsCond, Inventory.Storage, Inventory.StorageOther, Inventory.InclusiveDates, Inventory.FileFormat, Inventory.SoftHardWare, Inventory.IMBox, Inventory.IMBarcode
    FROM Inventory
    WHERE (((Inventory.Department)=[Forms]![AllQuery]![Department] & "*") AND 
    ((Inventory.OriginalCopy)=[Forms]![AllQuery]![OriginalCopy] & "*") AND 
    ((Inventory.RecordsLoc)=[Forms]![AllQuery]![RecordsLoc] & "*") AND 
    ((Inventory.PhysicalOther)=[Forms]![AllQuery]![PhysicalOther] & "*") AND 
    ((Inventory.ElectronicLoc)=[Forms]![AllQuery]![ElectronicLoc] & "*") AND 
    ((Inventory.ElectronicOther)=[Forms]![AllQuery]![ElectronicOther] & "*") AND 
    ((Inventory.LastNameMaintainer)=[Forms]![AllQuery]![LastNameMaintainer] & "*") AND 
    ((Inventory.Description)=[Forms]![AllQuery]![Description] & "*") AND 
    ((Inventory.Status)=[Forms]![AllQuery]![Status] & "*") AND
    ((Inventory.Restrictions)=[Forms]![AllQuery]![Restrictions] & "*") AND 
    ((Inventory.RecordsMed)=[Forms]![AllQuery]![RecordsMed] & "*") AND 
    ((Inventory.MediumOther)=[Forms]![AllQuery]![MediumOther] & "*") AND 
    ((Inventory.Arrangement)=[Forms]![AllQuery]![Arrangement] & "*") AND 
    ((Inventory.ArrangementOther)=[Forms]![AllQuery]![ArrangementOther] & "*") AND 
    ((Inventory.RecordsCond)=[Forms]![AllQuery]![RecordsCond] & "*") AND 
    ((Inventory.Storage)=[Forms]![AllQuery]![Storage] & "*") AND 
    ((Inventory.StorageOther)=[Forms]![AllQuery]![StorageOther] & "*") AND 
    ((Inventory.InclusiveDates)=[Forms]![AllQuery]![InclusiveDates] & "*") AND 
    ((Inventory.FileFormat)=[Forms]![AllQuery]![FileFormat] & "*") AND 
    ((Inventory.SoftHardWare)=[Forms]![AllQuery]![SoftHardWare] & "*") AND 
    ((Inventory.IMBox)=[Forms]![AllQuery]![IMBox] & "*") AND 
    ((Inventory.IMBarcode)=[Forms]![AllQuery]![IMBarcode] & "*"));
    And the following VBA code on the form:

    Code:
    Option Compare Database
    
    
    Private Sub Detail_Click()
    
    End Sub
    
    Private Sub Form_Load()
    
    'Does not allow additions, deletions or edits on search form
    
    DoCmd.GoToRecord , , acNewRec
    
    Me.AllowAdditions = True
    Me.AllowDeletions = False
    Me.AllowEdits = False
    
    End Sub
    
    Sub FindInControls()
    
    'Allows for a multi-field keywords search across all fields and null fields
    
    Dim ctl As Control
    Dim strSearch As String
    
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
            If Nz(ctl.Value, "") <> "" Then
                If Len(strCriteria) > 0 Then strCriteria = strCriteria & " AND "
                strCriteria = strCriteria & ctl.Name & " Like '" & ctl.Value & "*'"
            End If
        End If
    Next ctl
    
    End Sub
    But, it is still not giving me results. The other VBA stuff works- no additions, deletions, start on blank record (so people can input their search values).

    Some of the fields are not just plain textboxes-- Some are date and others are lookup... Would this make a difference?

    Thank you!
    Last edited by alemonlemon; 02-14-13 at 16:16.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by alemonlemon View Post
    But, it is still not giving me results. The other VBA stuff works- no additions, deletions, start on blank record (so people can input their search values).

    Some of the fields are not just plain textboxes-- Some are date and others are lookup... Would this make a difference?
    1. When the criteria (i.e. WHERE section of the query) is assembled, you still need to merge it with the other sections: SELECT section, ORDER BY section, etc.

    2. When the criteria is complete, you can either:
    a) Complete the SQL expression (SELECT ... FROM ... WHERE (criteria) ORDER BY ...; ), then change the SQL expression of the original query (warning: such a change is permanent):
    Code:
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs("Name of the Query")
    qdf.SQL = strSQL ' strSQL = the string variable containing the complete SQL expression.
    qdf.Close ' The query now has changed and can be used.
    Set qdf = Nothing
    b) Derive a second query from the original:
    Code:
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    strSQL = "SELECT * FROM [Name of the original Query] WHERE " & strCriteria
    Set qdf = CurrentDb.CreateQueryDef("Name of the new Query")
    qdf.SQL = strSQL ' strSQL = the string variable containing the complete SQL expression.
    qdf.Close ' The new query was created and can be used.
    Set qdf = Nothing
    3. If controls of various types are in use, you cannot select them using:
    Code:
    If ctl.ControlType = acTextBox Then
    which restricts the selection to TextBox controls. You can either:
    a) Use a special value (i.e. "@") that you store in the Tag property of each controls that can be used to build the criteria:
    Code:
    If ctl.Tag = "@" Then
    b) If the Tag property is already used for another purpose, you can use the HelpContextId pproperty of the controls. This property default value is 0 (zero) and it is almost never used, except if you want to build a specific compiled help file (.chm) for the application, which is very unlikely:
    Code:
    If ctl.HelpContextId <> 0 Then
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •