Simplify

[Noel2]

Does anyone know how I can simplify the following sub routine? Please let me know. I am just finding the subroutine to be too unreadable, it works and everything but it is just too cumbersome for those who don't know what is going on with it. At the bottom I have included the Function that I am using in the module which is referenced in the sub routine:

Sub BuildRecSource()
' subroutine in expression

Dim getRowSce As String
Dim getOrdrcls As String
Dim getWhrcls As String
Dim getCondi As String
Dim getCond As String
Dim stdCod As String
Dim stdCodi As String
Dim stdCdm As String
Dim stdCCm As String
Dim stdWhrCls As String

If (Nz(Forms![daily_list]![List4])) = "" Then
If (Forms![daily_list]![List9]) = "All" Then
stdCdm = ""
stdCCm = ""
stdWhrCls = freedajuice("[wrk_ordr]", "", "", " WHERE &quot
Else
stdWhrCls = " WHERE "
If (Nz(Forms![daily_list]![wrk_ordr])) = "" Then
stdCdm = ""
stdCCm = ""
Else
stdCod = " AND "
stdCdm = "("
stdCCm = &quot"
End If
End If
Else
stdWhrCls = " WHERE "
stdCod = " AND "
If (Nz(Forms![daily_list]![wrk_ordr])) = "" Then
stdCodi = ""
If (Forms![daily_list]![List9]) = "All" Then
stdCdm = ""
stdCCm = ""
Else
stdCdm = "("
stdCCm = &quot"
End If
Else
stdCodi = " AND "
stdCdm = "("
stdCCm = &quot"
End If
End If

getRowSce = "Select DISTINCTROW daily.id, daily.techid, daily.dt, daily.tm_in, daily.tm_out, daily.addr, daily.wrk_ordr_num, daily.dscrpt, daily.cde FROM daily"
getWhrcls = stdWhrCls & stdCdm & freedajuice("List4", "", "", "((daily.dt)=" & "#" & [Forms]![daily_list]![List4] & "#)&quot
getCondi = freedajuice("wrk_ordr", "", "", stdCodi & "(daily.[wrk_ordr_num])='" & ([Forms]![daily_list]![wrk_ordr]) & "'&quot
getCond = freedajuice("List9", "All", "", stdCod & "(daily.techid)='" & ([Forms]![daily_list]![List9]) & "'&quot
getOrdrcls = stdCCm & " ORDER BY daily.techid, daily.wrk_ordr_num;"

List6.RowSource = getRowSce & getWhrcls & getCondi & getCond & getOrdrcls

End Sub

Function freedajuice(ByVal strjuci As String, strcd As String, strtru As String, strfls As String) As Variant
' function referred to in subroutine

Dim gstrfld As String

gstrfld = Nz(Forms("daily_list&quot(strjuci))

freedajuice = IIf((gstrfld) = strcd, strtru, strfls)

End Function

Thank you,
Noel

 

[mp9]

This is marginally simpler:

Sub BuildRecSource()
' subroutine in expression

 Dim getRowSce As String
 Dim getOrdrcls As String
 Dim getWhrcls As String
 Dim getCondi As String
 Dim getCond As String
 Dim stdCod As String
 Dim stdCodi As String
 Dim stdCdm As String
 Dim stdCCm As String
 Dim stdWhrCls As String

 stdCdm = ""
 stdCCm = ""
 
 If (Nz(Forms![daily_list]![List4])) = "" Then
    If (Forms![daily_list]![List9]) = "All" Then
        stdWhrCls = freedajuice("[wrk_ordr]", "", "", " WHERE ")
    Else
        stdWhrCls = " WHERE "
        If (Nz(Forms![daily_list]![wrk_ordr])) <> &quot;&quot; Then
            stdCod = &quot; AND &quot;
            stdCdm = &quot;(&quot;
            stdCCm = &quot;)&quot;
        End If
    End If
 Else
    stdWhrCls = &quot; WHERE &quot;
    stdCod = &quot; AND &quot;
    If (Nz(Forms![daily_list]![wrk_ordr])) = &quot;&quot; Then
        stdCodi = &quot;&quot;
        If (Forms![daily_list]![List9]) <> &quot;All&quot; Then
            stdCdm = &quot;(&quot;
            stdCCm = &quot;)&quot;
        End If
    Else
        stdCodi = &quot; AND &quot;
        stdCdm = &quot;(&quot;
        stdCCm = &quot;)&quot;
    End If
 End If
 
    getRowSce = &quot;Select DISTINCTROW daily.id, daily.techid, daily.dt, daily.tm_in, daily.tm_out, daily.addr, daily.wrk_ordr_num, daily.dscrpt, daily.cde FROM daily&quot;
    getWhrcls = stdWhrCls & stdCdm & freedajuice(&quot;List4&quot;, &quot;&quot;, &quot;&quot;, &quot;((daily.dt)=&quot; & &quot;#&quot; & [Forms]![daily_list]![List4] & &quot;#)&quot;)
    getCondi = freedajuice(&quot;wrk_ordr&quot;, &quot;&quot;, &quot;&quot;, stdCodi & &quot;(daily.[wrk_ordr_num])='&quot; & ([Forms]![daily_list]![wrk_ordr]) & &quot;'&quot;)
    getCond = freedajuice(&quot;List9&quot;, &quot;All&quot;, &quot;&quot;, stdCod & &quot;(daily.techid)='&quot; & ([Forms]![daily_list]![List9]) & &quot;'&quot;)
    getOrdrcls = stdCCm & &quot; ORDER BY daily.techid, daily.wrk_ordr_num;&quot;

    List6.RowSource = getRowSce & getWhrcls & getCondi & getCond & getOrdrcls

End Sub

Function freedajuice(ByVal strjuci As String, strcd As String, strtru As String, strfls As String) As Variant
    ' function referred to in subroutine

    Dim gstrfld As String

    gstrfld = Nz(Forms(&quot;daily_list&quot;)(strjuci))
  
    freedajuice = IIf((gstrfld) = strcd, strtru, strfls)

End Function

Hope this helps a little - you may also want to test doing away with the

 stdCdm = ""
 stdCCm = ""

at the beginning altogether, since an unassigned string variable returns a zero-length string anyway (I think!).

http://www22.brinkster.com/accessory/