Multi-Field Search Button (Query) Showing Records With or Without Missing Fields

I have a database with only 1 table, this table has 27+ fields and not all of them will contain a value (some will be empty). I have a button (“search alarm”) on a navigation form that performs a multi-field search, there are 6 different fields which the user can search by and the user can use from 1 to 6 criteria at the same time (please see photo). Search Form .
For example, I can search by just the Name, or the Name and the Tag etc etc and I want all the records that have those items to show even if there are some other missing fields in that record.

Below is the code that executes when the button is clicked.

varI = DLookup("Tag", "tblAlarms", "Tag Like '*" & Forms![frmHomePage]!inputTag & "*'")
varJ = DLookup("Name", "tblAlarms", "Name Like '*" & Forms![frmHomePage]!inputName & "*'")
varX = DLookup("Plant_Identification_Number", "tblAlarms", "Plant_Identification_Number Like '*" & Forms![frmHomePage]!inputPlantID & "*'")
varY = DLookup("Priority", "tblAlarms", "Priority Like '*" & Forms![frmHomePage]!inputPriority & "*'")
varZ = DLookup("Group", "tblAlarms", "Group Like '*" & Forms![frmHomePage]!inputGroup & "*'")
varK = DLookup("Classification", "tblAlarms", "Classification Like '*" & Forms![frmHomePage]!inputClass & "*'")

        If Not IsNull(varI) And Not IsNull(varJ) And Not IsNull(varX) And Not IsNull(varY) And Not IsNull(varZ) And Not IsNull(varK) Then
            DoCmd.OpenForm "frmAlarms", acNormal, "", "Tag Like ""*"" & [Forms]![frmHomePage]![inputTag] & ""*"" and Name Like ""*"" & [Forms]![frmHomePage]![inputName] & ""*"" and Plant_Identification_Number Like ""*"" & Forms![frmHomePage]!inputPlantID & ""*"" and Priority Like ""*"" & Forms![frmHomePage]!inputPriority & ""*"" and Group Like ""*"" & Forms![frmHomePage]!inputGroup & ""*"" and Classification Like ""*"" & Forms![frmHomePage]!inputClass & ""*""", acEdit, acNormal
            DoCmd.Close acForm, "frmHomePage", acSaveNo
            Me.Visible = False
        Else
            MsgBox "0 Search Results Were Found!"
            Me.Visible = True
        End If

My problem is that I cannot run the query/search (DoCmd.OpenForm line) when there are missing fields in the data tables.

I have tried removing the ‘If’ condition so that it will run the command regardless of whether a field is missing or not however ms access does not return any records unless the record contains data in all fields. Is there a way around this?

I have thought of the following:

Having ‘If’ statements to execute different queries depending on the data that has been input but with 6 different fields to search from that’s 720 different combinations.
Building a query string (DoCmd.OpenForm line in code) dependent on the search input, but I can’t think of a way for the code to know when to put an ‘&’ in between.
Tried having the button just run a query but again the problem is that if there are missing fields in a record, that record wont show even if the search criteria matches. For example, if I search for all records with the tag = 1234, these records will only show if all 27+ fields within that record contain a value.
Could possibly put “n/a” in all the fields that are empty, but is there a way of automatically doing this? As there is a user entry part of this database and it would be ideal if it would automatically populate empty fields with “n/a” upon a new record creation. Though this will make the table/data look a bit messy so would prefer a way around the search query and missing fields.

Any help would be much appreciated! Thanks in advance.

Answer

The string concatenation is not correct. Consider:

DoCmd.OpenForm "frmAlarms", acNormal, , "[Tag] LIKE '*" & Me.inputTag & _
          "*' AND [Name] LIKE '*" & Me.inputName & _
          "*' AND Plant_Identification_Number LIKE '*" & Me.inputPlantID & _
          "*' AND Priority LIKE '*" & Me.inputPriority & _
          "*' AND [Group] LIKE '*" & Me.inputGroup & _ 
          "*' AND Classification LIKE '*" & Me.inputClass & *'", acEdit, acNormal

LIKE and wildcard will not work properly on number field.

Handling empty (Null) fields in record options:

calculation in query that returns a value if field is null – the “N/A” idea – and apply filter criteria to that field
include OR Is Null in the criteria for each field, parentheses around both parts of OR phrase will be critical

However, if you are going to use VBA to build criteria string (as opposed to a dynamic parameterized query object), then conditionally building string is probably best approach. Don’t include a criteria if no input in textbox. Review http://allenbrowne.com/ser-62.html

Tag and Name and Group are reserved words. Should not use reserved words as names to avoid unexpected results. Enclosing in [ ] should resolve but better to avoid.

Advertisement

Answer