Hi all, hope your day is going well.
I've spent some time now developing the functionality for a database my company will be using going forward. I want to optimized it and de-clutter it as much as possible and I was wondering if I should focus on optimizing query design or shifting from queries into VBA functions. As it stands right now I have ~40 queries, many of which are just used to find something simple as a criteria for another query. Let me give you a few examples to explain...
I have a query, 'qryFindMostRecentReportingDate', that's entire purpose is to find the most recently submitted report for each different portfolio we manage. To take it one step further, I have another query 'qryFindMaxMostRecentReportingDate' to find the portfolio that has submitted a report most recently. I can simplify this process by writing code like the following:
Code:
Public Function getMostRecentReportedDate(Optional mandateID_Key As Variant = Null)
If IsNull(mandateID_Key) Then
getMostRecentReportedDate = DMax("[reportingDate]", "tblMandateReports")
Else: getMostRecentReportedDate = DMax("[reportingDate]", "tblMandateReports", "[mandateID_FK] = " & mandateID_Key)
End If
End Function
My question, then, is it worth it to convert as many of my queries into VBA functions as possible, which can just be called as needed?