How to Convert query with IIF statement into trans-SQL for an ADP form

[Lin100]

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 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] Is Null,[Department_Name],[Forms]![Selector]![Dept])) AND
([1_Job - Parent].SONumber=IIf([Forms]![Selector]![so] Is Null,[sonumber],[Forms]![Selector]![so])));

Everything below works fine.

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

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

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

Now. When I take the above 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.

 

[cmmrfrds]

you can use a Case Statement. I am not sure is will resolve Form names - the database names like Department_Name are fine, but you can try.

SELECT [1_Job - Parent].Department_Name,
[1_Job - Parent].SONumber
FROM [1_Job - Parent]
WHERE (([1_Job-Parent].Department_Name=
(Case When [Forms]![Selector]![Dept] Is Null then[Department_Name] else [Forms]![Selector]![Dept] End) AND
([1_Job - Parent].SONumber=
(Case When [Forms]![Selector]![so] Is Null Then [sonumber] else [Forms]![Selector]![so] End );