×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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.


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

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 );


 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close