Looking for a better approach to keeping long parameter queries organized in MS Access

Question

I am building a database to help manage documents (more info at the end about this). I have been really spinning my wheels with query-by-form parameter criteria. This is the first time I have tried building a search form. I am able to build the search form query using the Query Designer GUI and for the most part, I believe it is accomplishing what I need it to do. However, I'm not content with this option because I have found that the query is effectively unmanageable when I try to edit it.

Sometimes, the query will not open in design view at all. The DB just freezes up. If it does open, what I see is that criteria lines have permutated/duplicated hundreds and hundreds of times. There are also many additional helper columns added by Access. Disclaimer: I am saying that Access is adding these extra rows and columns, but I'm sure it's more of a case of me putting garbage in and getting garbage out.

Applicable database parts

Query Designer view showing all the replicating criteria.

search form

SELECT
    tbl_Projects.Proj_ID, 
    tbl_Projects.ClientName, 
    tbl_Projects.ProjTitle, 
    tbl_Projects.City, 
    tbl_Projects.State, 
    tbl_Projects.Proj_PM, 
    tbl_Projects.Proj_Struc, 
    tbl_Docs.Doc_Key, 
    tbl_Docs.DocType, 
    tbl_Docs.DocName, 
    tbl_Docs.StrucType, 
    tbl_Docs.ReviewLevel
FROM ((tbl_Projects 
LEFT JOIN list_Qry_NameSelector ON tbl_Projects.Proj_PM = list_Qry_NameSelector.ID_Text) 
LEFT JOIN list_Qry_NameSelector AS list_Qry_NameSelector_1 ON tbl_Projects.Proj_Struc = list_Qry_NameSelector_1.ID_Text) 
LEFT JOIN tbl_Docs ON tbl_Projects.Proj_Key = tbl_Docs.Proj_Key
WHERE (((tbl_Projects.Proj_ID)=Forms!frm_SearchMain!tb_Proj_ID Or 
Forms!frm_SearchMain!tb_Proj_ID Is Null) And 
((tbl_Projects.ClientName)=Forms!frm_SearchMain!cbx_ClientName Or 
Forms!frm_SearchMain!cbx_ClientName Is Null) And 
((tbl_Projects.ProjTitle)=Forms!frm_SearchMain!tb_ProjTitle Or Forms!frm_SearchMain!tb_ProjTitle Is Null) And 
((tbl_Projects.City)=Forms!frm_SearchMain!cbx_City Or 
Forms!frm_SearchMain!cbx_City Is Null) And 
((tbl_Projects.State)=Forms!frm_SearchMain!cbx_State Or 
Forms!frm_SearchMain!cbx_State Is Null) And 
((tbl_Projects.Proj_PM)=Forms!frm_SearchMain!cbx_Proj_PM Or 
Forms!frm_SearchMain!cbx_Proj_PM Is Null) And 
((tbl_Projects.Proj_Struc)=Forms!frm_SearchMain!cbx_Proj_Struc Or 
Forms!frm_SearchMain!cbx_Proj_Struc Is Null) And 
((tbl_Docs.Doc_Key)=Forms!frm_SearchMain!tb_Doc_Key Or 
Forms!frm_SearchMain!tb_Doc_Key Is Null) And 
((tbl_Docs.DocType)=Forms!frm_SearchMain!cbx_DocType Or 
Forms!frm_SearchMain!cbx_DocType Is Null) And 
((tbl_Docs.DocName)=Forms!frm_SearchMain!tb_DocName Or 
Forms!frm_SearchMain!tb_DocName Is Null) And 
((tbl_Docs.StrucType)=Forms!frm_SearchMain!cbx_StrucType Or 
Forms!frm_SearchMain!cbx_StrucType Is Null) And 
((tbl_Docs.ReviewLevel)=Forms!frm_SearchMain!cbx_ReviewLevel Or 
Forms!frm_SearchMain!cbx_ReviewLevel Is Null));

These are the tables/fields/criteria I am using in my query. the tbl_sheets fields are not included yet.

TableName	TableField	ControlName	Criteria
tbl_Projects	Proj_ID	tb_Proj_ID	Forms!frm_SearchMain!tb_Proj_ID Or Forms!frm_SearchMain!tb_Proj_ID Is Null
tbl_Projects	ClientName	cbx_ClientName	Forms!frm_SearchMain!cbx_ClientName Or Forms!frm_SearchMain!cbx_ClientName Is Null
tbl_Projects	ProjTitle	tb_ProjTitle	Forms!frm_SearchMain!tb_ProjTitle Or Forms!frm_SearchMain!tb_ProjTitle Is Null
tbl_Projects	City	cbx_City	Forms!frm_SearchMain!cbx_City Or Forms!frm_SearchMain!cbx_City Is Null
tbl_Projects	State	cbx_State	Forms!frm_SearchMain!cbx_State Or Forms!frm_SearchMain!cbx_State Is Null
tbl_Projects	Proj_PM	cbx_Proj_PM	Forms!frm_SearchMain!cbx_Proj_PM Or Forms!frm_SearchMain!cbx_Proj_PM Is Null
tbl_Projects	Proj_Struc	cbx_Proj_Struc	Forms!frm_SearchMain!cbx_Proj_Struc Or Forms!frm_SearchMain!cbx_Proj_Struc Is Null
tbl_Docs	Doc_Key	tb_Doc_Key	Forms!frm_SearchMain!tb_Doc_Key Or Forms!frm_SearchMain!tb_Doc_Key Is Null
tbl_Docs	DocType	cbx_DocType	Forms!frm_SearchMain!cbx_DocType Or Forms!frm_SearchMain!cbx_DocType Is Null
tbl_Docs	DocName	tb_DocName	Forms!frm_SearchMain!tb_DocName Or Forms!frm_SearchMain!tb_DocName Is Null
tbl_Docs	StrucType	cbx_StrucType	Forms!frm_SearchMain!cbx_StrucType Or Forms!frm_SearchMain!cbx_StrucType Is Null
tbl_Docs	ReviewLevel	cbx_ReviewLevel	Forms!frm_SearchMain!cbx_ReviewLevel Or Forms!frm_SearchMain!cbx_ReviewLevel Is Null
tbl_Docs	SubmitDate	cbx_SubmitDate	Forms!frm_SearchMain!cbx_SubmitDate Or Forms!frm_SearchMain!cbx_SubmitDate Is Null
tbl_Sheets	Sheet_PK	tb_Sheet_PK	Forms!frm_SearchMain!tb_Sheet_PK Or Forms!frm_SearchMain!tb_Sheet_PK Is Null
tbl_Sheets	SheetNum	cbx_SheetNum	Forms!frm_SearchMain!cbx_SheetNum Or Forms!frm_SearchMain!cbx_SheetNum Is Null
tbl_Sheets	SheetName	tb_SheetName	Forms!frm_SearchMain!tb_SheetName Or Forms!frm_SearchMain!tb_SheetName Is Null
tbl_Sheets	DD_1	cbx_DD_1	Forms!frm_SearchMain!cbx_DD_1 Or Forms!frm_SearchMain!cbx_DD_1 Is Null
tbl_Sheets	Sht_DrawnBy	cbx_Sht_DrawnBy	Forms!frm_SearchMain!cbx_Sht_DrawnBy Or Forms!frm_SearchMain!cbx_Sht_DrawnBy Is Null
tbl_Sheets	Sht_ApproveBy	cbx_Sht_ApproveBy	Forms!frm_SearchMain!cbx_Sht_ApproveBy Or Forms!frm_SearchMain!cbx_Sht_ApproveBy Is Null

There simply has to be something I am misunderstanding or not doing correctly. Either criteria syntax or using queries wrong altogether.

A lot of online searching later, I came across this similar post: https://www.reddit.com/r/MSAccess/comments/khd4gi/how_to_avoid_access_queries_criteria_splitting/ The first person to respond mentions using permutations stored in lookup tables but doesn't explain this concept in further detail or how to accomplish it. "Why are you trying to use logic gates without a lookup table?"

Is this "lookup table" the option that I should be using? Should I be using VBA instead to build queries "on-the-fly"? I'm not sure how to do that really though. I have penciled out how I believe this would work but I must be misinterpreting the concept because wouldn't that just be recreating the same SQL statement that I have now?

Any help here would be sincerely appreciated. I have been either fiddling with this or googling information about it off and on since November. Thanks

Supplementary information

Project background: The database only stores links to where the documents are saved. I'm not using attachment fields to store documents. This project is primarily for my own personal use. I realize there are commercially available document management options. For me though, it is an engaging project that I am using to learn more.

My background: I've built 3 or 4 small databases, each intended for only a few people to use. I'm very good with excel and excel VBA. I can (slowly) google/muddle my way through Access VBA. I took two 'just for fun' level MS Access online classes (like 6 weeks each) a few years ago. In summary, I'm still really green when it comes to MS Access.

Answer 1

The simple approach here is to NEVER uses forms! expressions in a query.

I mean, the instant you do that, then you have a query that is married to one form, and if that form is not open, then the query will fail.

Worse yet, is then you can't use that query for anything else!

So, base the report (or form) on a clean, simple query, one without any forms!expresisons.

You find MASSIVE increases in re-use, and MASSIVE increases in developer productivity.

The next bonus is that you can with GREAT ease have each of the parameters in that form be optional, and once again, this means far more flexibility, and once again means you wind up with a nice clean query.

In other words, build the where clause using VBA code. So, is this some work? yes, it is "some" work and "some" code, but is far less messy, and far more maintainable then these "monster" queries with boatloads of forms! expressions that become near impossible to maintain, change, and re-use.

As noted, the bonus feature that for filter controls left alone (empty) then you ignore them, and thus the optional filtering also becomes far more easy to deal with.

So, say we have this form:

So, in place of some massive super messy query?

We write code. The cost is about 3 lines of code per filter option.

Hence, the code behind is this:

Private Sub cmdSearch_Click()

    Dim strSQL      As String
    Dim strWhere    As String
    
    strSQL = "SELECT * FROM tblHotels "
    
    ' first name
    If sFirstName <> "" Then
        strWhere = "(FirstName like '" & sFirstName & "*' ) "
    End If
    
    
    ' last name
    If sLastName <> "" Then
        If strWhere <> "" Then strWhere = strWhere & " AND "
        strWhere = strWhere & "(LastName like '" & sLastName & "*' ) "
    End If
    
    ' combo box for city
    If cboCity <> "" Then
       If strWhere <> "" Then strWhere = strWhere & " AND "
       strWhere = strWhere & "(City = '" & cboCity & "') "
    End If
    
    ' include only active hotels check box
    If ChkActive Then
       If strWhere <> "" Then strWhere = strWhere & " AND "
       strWhere = strWhere & "(Active = true)"
    End If
    
    ' Hotel name
    If txtHotelName <> "" Then
       If strWhere <> "" Then strWhere = strWhere & " AND "
       strWhere = strWhere & "(HotelName like '" & txtHotelName & "*') "
    End If
    
    ' Must have description check box
    If chkDescription Then
       If strWhere <> "" Then strWhere = strWhere & " AND "
       strWhere = strWhere & "(Description is not null)"
    End If
    
    ' we now have valid filter, open a report
    '
    ' docmd.OpenReport "rptHotels", acviewPreview,,strWhere
    
    
    If strWhere <> "" Then
       strSQL = strSQL & " WHERE " & strWhere
    End If
    
    strSQL = strSQL & " ORDER BY HotelName"
    
    Me.RecordSource = strSQL
    

End Sub

So, as you can see, we now can have 3 or 25 filters. The query has no such forms! expressions.

So, break the connection from the query and the form. They should not be tied or married together anyway.

And say in above, I wanted to add a booking date range?

Then I just drop in two date controls, and I have this:

So, now at the end of our existing filter code, then I can have this:

    If dtStart <> "" Then
        If strWhere <> "" Then strWhere = strWhere & " AND "
       strWhere = strWhere & "(BookingDate BETWEEN " & qudate(dtStart) & " AND " & qudate(dtEnd) & ") "
    End If

So, now I have a optional date range I can use. And I don't like trying to format dates, so I have a global "helper" function called qudate. (and I even have one for quotes also - saves some keyboards).

So, qudate is this:

Public Function quDated(dt As Date) As String

   ' return formatted date
   
   quDated = "#" & Format(dt, "mm\/dd\/yyyy") & "#"
  
   
End Function

So, it don't matter if you have 2, or 20?

Just write out the filter in code. That filter can then be used to launch a report, a form, or whatever. (use the "where" clause of the open report).

And to be clear? This type of filter form is NOT bound to the data table. You make the selections and then hit a button. That button will/then can launch the actual data form for editing.

So, that code would be

Docmd.OpenForm "myDataEditForm",,,strWhere