How I build my SQL strings

[disruptivehair]

This is the entire module I wrote for building SQL strings for my application. All of this was previously contained in a single sub so I broke it up into individual 'tasks', if that makes any sense. Since I'm still pretty new at this I don't know if that's good or bad practice.

Good, bad, inefficient, maintainable? Do your worst; I'm prepared to take it! There are several functions so if you have any questions about how they work or if I've left anything out please let me know.

TIA.

' This function fetches the subfilter condition from the Conditions table
' on SQL Server and returns it to the calling sub only if there is a selected
' value on the subfilter.  If there is no selected value or the selected
' value is 'All', the function returns nothing.  The sub-filter is the combo
' box on Criteria Selection where the user can choose values for Application
' Type, Critical Illness, Missing Items, or Sum Assured.
 
Public Function GetCondition(strSubFilterValue As String, strSQL As String) As String
 
' Check to make sure connection still exists.  If it doesn't, set it up.  If connection
' is closed then open it.
' Call CheckConnection
 
Dim strCondition As String
 
' // If the LENGTH of the subfilter value is 0 then nothing is selected, strCondition
' is set to empty; if the value is 'ALL' then no filtering needs to be applied and
' strCondition is likewise set to empty.
If Len(strSubFilterValue) = 0 Then
    strCondition = ""
ElseIf strSubFilterValue = "All" Then
    strCondition = ""
    
' // If the LENGTH of the subfilter value is greater than zero this indicates the user
' has selected something, so a recordset is opened and the SQL source passed to the function
' is executed.
ElseIf Len(strSubFilterValue) > 0 Then
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnConn, adOpenForwardOnly, adLockReadOnly, adCmdText
    strCondition = rst.GetString
    rst.Close
    Set rst = Nothing
End If
 
GetCondition = strCondition
 
End Function

' This function uses the tab the user is on and the value of the filter they've
' selected to construct part of the 'WHERE' clause SQL Server will need to return
' the appropriate data.  Example 'filter' values are York, Bankhall, CONTROL, etc.
' This function does not actually include 'WHERE' or 'AND'; that part of the string
' is constructed by another function.
 
Public Function GetClause(strTabValue As String, strFilterValue As String) As String
 
' Check to make sure connection still exists.  If it doesn't, set it up.  If connection
' is closed then open it.
' Call CheckConnection
 
    Dim strClause As String
 
    ' Builds a SQL clause based on what tab the user is currently using; if the tab value
    ' is "Headof" then no clause is built as no filter is actually being used.
    Select Case strTabValue
        Case Is = "HeadOf"
            strClause = ""
        Case Is = "Distributor"
            strClause = "Distributor = " & "'" & "'" & strFilterValue & "'" & "'"
        Case Is = "Site"
            strClause = "Site = " & "'" & "'" & strFilterValue & "'" & "'"
        Case Is = "Team"
            strClause = "Team = " & "'" & "'" & strFilterValue & "'" & "'"
        Case Is = "Parent"
            strClause = "ParentCompany = " & "'" & "'" & strFilterValue & "'" & "'"
        Case Else
            strClause = ""
    End Select
    
    GetClause = strClause
 
End Function

' This function uses the values of strCondition and strClause plus
' what tab the user is on to determine how to build the parameter string.
' On the HeadOf tab, the only time there will be a WHERE clause is if
' a sub-filter value is selected; otherwise there is no WHERE clause.
'
' On other tabs if a sub-filter value is selected it must be preceded by AND,
' not WHERE, as you can only use the WHERE keyword once in a normal query
' and we'll already be using WHERE for Distributor, Site, Team, or ParentCompany.
 
Public Function GetParameter(strCondition As String, strTabValue As String _
    , strClause As String) As String
 
    ' Check to make sure connection still exists.  If it doesn't, set it up.  If connection
    ' is closed then open it.
    'Call CheckConnection
    
    Dim strParameter As String
    
    Select Case strTabValue
        ' If the user is on HeadOf and no condition is selected then no WHERE clause is built
        ' If a condition (subfilter) is selected then a WHERE clause is constructed
        Case Is = "HeadOf"
            If strCondition = "" Then
                strParameter = ""
            Else:
                strParameter = " WHERE " & strCondition
            End If
        ' If the user is on any other tab and no condition (subfilter) is selected then the
        ' WHERE clause is constructed using only the selected distributor, site, etc.
        ' If a condition (subfilter) is selected then an AND clause is tacked onto the
        ' end of the WHERE clause
        Case Is = "Distributor", "Site", "Team", "Parent"
            If strCondition = "" Then
                strParameter = " WHERE " & strClause
            Else: If Len(strCondition) > 0 Then strParameter = " WHERE " & strClause & " AND " & strCondition
            End If
        ' This should never happen, but in case it does GetParameter returns a zero-length string
        Case Else
            strParameter = ""
    End Select
    
    GetParameter = strParameter
 
End Function

' Executes GetClause, GetCondition, and GetParameter to build an appropriate
' SQL string based on the user's selections on the CriteriaSelection form.
' This is the string that will be passed to the appropriate stored procedure
' as its parameter.
 
Public Function DeriveWhereClause() As String
 
' Check to make sure connection still exists.  If it doesn't, set it up.  If connection
' is closed then open it.
 Call CheckConnection
 
Dim strParameter As String          ' Holds value of parameter
Dim strCondition As String          ' Holds value of condition
Dim strClause As String             ' Holds value of clause
Dim strTabValue As String           ' Holds what tab user is currently on
Dim strFilterValue As String        ' Holds value of filter, i.e. 'York', 'Bankhall', etc
Dim strSubFilterValue As String     ' Holds value of subfilter, i.e. 'paper', 'dirty', etc
Dim strAddInfoType As String        ' Holds value of subfilter type, i.e. 'Missing Items', 'Critical Illness', etc
Dim strSQL As String                ' Source SQL
 
' Call global variable functions and assign results to local variables
strTabValue = GetgVarActiveTab()
strFilterValue = GetgVarPrimInfoValue()
strSubFilterValue = GetgVarAddInfoValue()
strAddInfoType = GetgVarAddInfoType()
 
' Initialize strSQL
strSQL = "SELECT SQLResult FROM Conditions WHERE QueryField = " _
    & "'" & strAddInfoType & "'" & " AND Value = " & "'" & strSubFilterValue & "'"
 
' Call GetCondition function and assign result to strCondition variable
If Len(strSubFilterValue) > 0 Then
    strCondition = GetCondition(strSubFilterValue, strSQL)
Else
    strCondition = ""
End If
 
' Call GetClause function and assign result to strClause variable
If Len(strTabValue) > 0 Then
    strClause = GetClause(strTabValue, strFilterValue)
Else
    strClause = ""
End If
 
' Call GetParameter function and assign result to strParameter variable
strParameter = GetParameter(strCondition, strTabValue, strClause)
 
DeriveWhereClause = strParameter
 
End Function

Public Function MakeSQLString() As String
 
    ' Check to make sure connection still exists/is open.
    Call CheckConnection
 
    Dim strSQL As String
    Dim strSQLObject As String
    Dim strSource As String
    
    Dim rst As ADODB.Recordset
    
    strSource = "SELECT SQLObject FROM tblReports WHERE ReportName = " & _
        "'" & GetgVarReportSelected() & "'"
    
    Set rst = cnConn.Execute(strSource)
    strSQLObject = rst.GetString
    
    ' Status History and CELT Data Summary cannot be filtered in any
    ' way so their SQL strings are hard-coded here.  Weekly Summary
    ' is not handled by this function at all.
    Select Case GetgVarReportSelected()
        Case Is = "Status History"
            strSQL = "EXEC sp_StatusPositionSummary"
        Case Is = "CELT Data Summary"
            strSQL = "EXEC sp_FEWATCELT"
        Case Else
            strSQL = "EXEC " & strSQLObject & " @Filter = " _
                & "'" & DeriveWhereClause & "'"
    End Select
      
    MakeSQLString = strSQL
    
    rst.Close
    Set rst = Nothing
    
End Function