How to Convert a SELECT query with IIF statement into transact SQL for an ADP Form

How to Convert SELECT query with IIF statement into transact SQL for an ADP Form.
Access 2002 and SQL 2000 Server.

I have an Access database named Supply.MDB
I have two combo boxes (Dept, and SO) on a form.
I also have a SELECT query named Q_FilteringQuery with IIF statement showed below.

SELECT [1_Job - Parent].Department_Name, [1_Job - Parent].SONumber
FROM [1_Job - Parent]
WHERE (([1_Job-Parent].Department_Name=IIf([Forms]![Selector]![Dept]  
                IsNull,[Department_Name],[Forms]![Selector]![Dept])) AND
([1_Job - Parent].SONumber=IIf([Forms]![Selector]![so] Is Null,[sonumber],[Forms]![Selector]![so])));

/////////////////////////////////////////////////////

Combo Box Name: Dept
Row Source: SELECT DISTINCT Q_FilteringQuery.Department_Name FROM Q_FilteringQuery ORDER BY Q_FilteringQuery.Department_Name;

Combo Box Name: So
Row Source: SELECT DISTINCT Q_FilteringQuery.SONumber FROM Q_FilteringQuery ORDER BY Q_FilteringQuery.SONumber;

/////////////////////////////////////////////////////

Private Sub Dept_AfterUpdate()
Me.Dept.Requery
Me.so.Requery
End Sub

Private Sub SO_AfterUpdate()
Me.Dept.Requery
Me.so.Requery
End Sub

/////////////////////////////////////////////////////

Everything from above works fine.

Now. When I take the above SELECT query that has IIF in the select statement, and converted to
transact SQL to be used in the ADP form, I get some very long VBA code. It works without bugs
but it is very long. Imagine if there are 4 combo boxes instead of 2, the code would be even longer!!

Question: Is there anyway to put code within the transact SQL to mimic the IIF statement ?
There are four different scenarios that Dept and SO combo box can have and that is why the transact SQL is so long

Dept                                     SO
Custom (Not Null)                 ______ (Is Null)
Custom (Not Null)                13056 (Not Null)
______ (Is Null)                   13056 (Not Null)
______ (Is Null)                   ______ (Is Null)

*******************************************************

Private Sub Dept_AfterUpdate()
Dim strSQL As String
Dim SQL_SO As String
Dim LgSO As Long
Dim strDept As String

If Not IsNull(Me.Dept) Then
strDept = CStr(Me.Dept)
End If

If Not IsNull(Me.so) Then
LgSO = CLng(Me.so)
End If

If Not IsNull(Me.Dept) And IsNull(Me.so) Then
strSQL = "SELECT DISTINCT [1_Job - Parent].Department_Name"
strSQL = strSQL & " FROM dbo.[1_Job - Parent] "
strSQL = strSQL & " WHERE dbo.[1_Job - Parent].Department_Name = '" & strDept & "'"
strSQL = strSQL & " ORDER BY Department_Name"
Me.Dept.RowSource = strSQL

SQL_SO = "SELECT DISTINCT [1_Job - Parent].SONumber"
SQL_SO = SQL_SO & " FROM dbo.[1_Job - Parent] "
SQL_SO = SQL_SO & " WHERE dbo.[1_Job - Parent].Department_Name = '" & strDept & "'"
SQL_SO = SQL_SO & " ORDER BY SONumber"
Me.so.RowSource = SQL_SO
Else
If Not IsNull(Me.Dept) And Not IsNull(Me.so) Then
strSQL = "SELECT DISTINCT [1_Job - Parent].Department_Name"
strSQL = strSQL & " FROM dbo.[1_Job - Parent] "
strSQL = strSQL & " WHERE dbo.[1_Job - Parent].Department_Name = '" & strDept & "'"
strSQL = strSQL & " AND SONumber =" & LgSO
strSQL = strSQL & " ORDER BY Department_Name"
Me.Dept.RowSource = strSQL

SQL_SO = "SELECT DISTINCT [1_Job - Parent].SONumber"
SQL_SO = SQL_SO & " FROM dbo.[1_Job - Parent] "
SQL_SO = SQL_SO & " WHERE dbo.[1_Job - Parent].Department_Name = '" & strDept & "'"
SQL_SO = SQL_SO & " AND SONumber =" & LgSO
SQL_SO = SQL_SO & " ORDER BY SONumber"
Me.so.RowSource = SQL_SO
Else
If IsNull(Me.Dept) And Not IsNull(Me.so) Then
strSQL = "SELECT DISTINCT [1_Job - Parent].Department_Name"
strSQL = strSQL & " FROM dbo.[1_Job - Parent] "
strSQL = strSQL & " WHERE dbo.[1_Job - Parent].SONumber = " & LgSO
strSQL = strSQL & " ORDER BY Department_Name"
Me.Dept.RowSource = strSQL

SQL_SO = "SELECT DISTINCT [1_Job - Parent].SONumber"
SQL_SO = SQL_SO & " FROM dbo.[1_Job - Parent] "
SQL_SO = SQL_SO & " WHERE dbo.[1_Job - Parent].SONumber = " & LgSO
SQL_SO = SQL_SO & " ORDER BY SONumber"
Me.so.RowSource = SQL_SO
Else
If IsNull(Me.Dept) And IsNull(Me.so) Then
strSQL = "SELECT DISTINCT [1_Job - Parent].Department_Name"
strSQL = strSQL & " FROM dbo.[1_Job - Parent] "
strSQL = strSQL & " ORDER BY Department_Name"
Me.Dept.RowSource = strSQL

SQL_SO = "SELECT DISTINCT [1_Job - Parent].SONumber"
SQL_SO = SQL_SO & " FROM dbo.[1_Job - Parent] "
SQL_SO = SQL_SO & " ORDER BY SONumber"
Me.so.RowSource = SQL_SO
End If
End If
End If
End If
End Sub

*******************************************************

Private Sub SO_AfterUpdate()
Dim SQL_Dept As String
Dim strSQL As String
Dim LgSO As Long
Dim strDept As String

If Not IsNull(Me.Dept) Then
strDept = CStr(Me.Dept)
End If

If Not IsNull((Me.so)) Then
LgSO = CLng(Me.so)
End If

If Not IsNull(Me.Dept) And IsNull(Me.so) Then
strSQL = "SELECT DISTINCT [1_Job - Parent].SONumber"
strSQL = strSQL & " FROM dbo.[1_Job - Parent] "
strSQL = strSQL & " WHERE dbo.[1_Job - Parent].Department_Name = '" & strDept & "'"
strSQL = strSQL & " ORDER BY SONumber"
Me.so.RowSource = strSQL

SQL_Dept = "SELECT DISTINCT [1_Job - Parent].Department_Name"
SQL_Dept = SQL_Dept & " FROM dbo.[1_Job - Parent] "
SQL_Dept = SQL_Dept & " WHERE dbo.[1_Job - Parent].Department_Name = '" & strDept & "'"
SQL_Dept = SQL_Dept & " ORDER BY Department_Name"
Me.Dept.RowSource = SQL_Dept
Else
If Not IsNull(Me.Dept) And Not IsNull(Me.so) Then
strSQL = "SELECT DISTINCT [1_Job - Parent].SONumber"
strSQL = strSQL & " FROM dbo.[1_Job - Parent] "
strSQL = strSQL & " WHERE dbo.[1_Job - Parent].Department_Name = '" & strDept & "'"
strSQL = strSQL & " AND SONumber =" & LgSO
strSQL = strSQL & " ORDER BY SONumber"
Me.so.RowSource = strSQL

SQL_Dept = "SELECT DISTINCT [1_Job - Parent].Department_Name"
SQL_Dept = SQL_Dept & " FROM dbo.[1_Job - Parent] "
SQL_Dept = SQL_Dept & " WHERE dbo.[1_Job - Parent].Department_Name = '" & strDept & "'"
SQL_Dept = SQL_Dept & " AND SONumber =" & LgSO
SQL_Dept = SQL_Dept & " ORDER BY Department_Name"
Me.Dept.RowSource = SQL_Dept
Else
If IsNull(Me.Dept) And Not IsNull(Me.so) Then
strSQL = "SELECT DISTINCT [1_Job - Parent].SONumber"
strSQL = strSQL & " FROM dbo.[1_Job - Parent] "
strSQL = strSQL & " WHERE dbo.[1_Job - Parent].SONumber = " & LgSO
strSQL = strSQL & " ORDER BY SONumber"
Me.so.RowSource = strSQL

SQL_Dept = "SELECT DISTINCT [1_Job - Parent].Department_Name"
SQL_Dept = SQL_Dept & " FROM dbo.[1_Job - Parent] "
SQL_Dept = SQL_Dept & " WHERE dbo.[1_Job - Parent].SONumber = " & LgSO
SQL_Dept = SQL_Dept & " ORDER BY Department_Name"
Me.Dept.RowSource = SQL_Dept
Else
If IsNull(Me.Dept) And IsNull(Me.so) Then
strSQL = "SELECT DISTINCT [1_Job - Parent].SONumber"
strSQL = strSQL & " FROM dbo.[1_Job - Parent] "
strSQL = strSQL & " ORDER BY SONumber"
Me.so.RowSource = strSQL

SQL_Dept = "SELECT DISTINCT [1_Job - Parent].Department_Name"
SQL_Dept = SQL_Dept & " FROM dbo.[1_Job - Parent] "
SQL_Dept = SQL_Dept & " ORDER BY Department_Name"
Me.Dept.RowSource = SQL_Dept
End If
End If
End If
End If
End Sub

*******************************************************
Known Issue:
      The alternative is to use Stored Procedure which I already have done and it works.
      I just wondered if there is anyway to mimic IIF within a SELECT transact SQL