SQL IIF Statement

[bradmaunsell]

I have a graphing routine that runs fine in Access. I moved code to MS WORD and find the SQL statement breaks. The problem appears to be that the IIF within the SQL SELECT statement will not work.

For Example: SELECT CalNum, IIF(Month=5,1,0) AS Cmp FROM Calls


Anybody have any feedback on the use of IIF within an SQL SELECT statement.

Thanks,
Brad

 

[jonscott8]

The IIF function will work fine when using SQL Pass-through to Access.

FYI - Month is the name of a function in Access. I'd recommend you change the field name or encapsulate it in brackets like so:

SELECT CalNum, IIF([Month]=5,1,0) AS Cmp FROM Call

Although, I dont believe that is your problem. Post your code. And by the SQL statement breaks, do you mean it triggers an error or just returns unexpected results? Jon Hawkins

 

[bradmaunsell]

Thanks for the response Jon.

Here is the SQL routine I am trying to run in WORD.

As mentioned earlier, this same Code runs in Access and is used to drive a graph using the MSGRAPH control.

In Access, the code runs the SELECT 6 times to figure out the number of support calls opened, closed and outstanding at the end of each of 6 months. The is a simple form for the operator to choose a customer from a combo as well as a month (1-12) and year.

This whole thing orks great in Access and I simply cut and pasted the code into WORD.

Here is the code:

'******************************************************************************
'REQUIRES REFERENCE:
'MICROSOFT ACTIVE X DATA OBJECTS LIBRARY 2.5
'MICROSOFT CHART CONTROL 6.0 (SP4) (OLEDB)
'******************************************************************************
'Option Explicit

Dim adoConn As Connection
Dim dbs As Recordset
Dim Rs As Recordset


Dim rst As Recordset, rstCalls As Recordset
Dim strSQL As String
Dim dteBegDate As Date, dteEndDate As Date
Dim BegMth As Integer, EndMth As Integer
Dim CusID As String
Dim i As Integer, intNumberOfMonths As Integer 'number of months within in the selected parameters
Dim CallsOpenedCount As Integer
Dim CallsClosedCount As Integer
Dim CallsInProgCount As Integer
Dim aryCalls(1 To 6, 1 To 7) '1 to 3 rows: 1="CallsHdrName, 2=#CallsOpened, 3=#CallsClosed, 4=#CallsInProg and 1 to 6 Cols = number calls for each (row) by month (col):
Dim r, c, row As Integer 'count rows and columns for array manipulation
Dim intDaysInThisMonth As Integer, intNumberDaysBegMonth As Integer
Dim strMMM As String
Dim intStartMonth As Integer

Private Sub Document_Open()
RefreshRecordsets
End Sub

Private Sub cmdSQL_Click()

If IsNull(cboCustomer) Or cboCustomer = "" Then
MsgBox "Select a CUSTOMER from the drop down."
Exit Sub
End If
If IsNull(cboMonth) Or cboMonth = "" Then
MsgBox "Select a cut-off MONTH from the drop down."
Exit Sub
End If
If IsNull(cboYear) Or cboYear = "" Then
MsgBox "Select a YEAR from the drop down."
Exit Sub
End If

GetRecords

End Sub

Private Sub RefreshRecordsets()
GetAllCustomerRecords
Populate_cboCustomers
Populate_cboMonth
Populate_cboYear
End Sub

Public Sub ADO_Connection()
Set adoConn = New ADODB.Connection
adoConn.ConnectionString = "DSN=ClienteleSource;UID=sa;PWD=access_to_sql;"
adoConn.Open
End Sub

Sub Populate_cboCustomers()
Do Until rst.EOF
If Not IsNull(rst!OrgName) Then
cboCustomer.AddItem rst!OrgName
End If
rst.MoveNext
Loop
rst.MoveFirst
End Sub

Sub Populate_cboMonth()
cboMonth.AddItem "Jan"
cboMonth.AddItem "Feb"
cboMonth.AddItem "Mar"
cboMonth.AddItem "Apr"
cboMonth.AddItem "May"
cboMonth.AddItem "Jun"
cboMonth.AddItem "Jul"
cboMonth.AddItem "Aug"
cboMonth.AddItem "Sep"
cboMonth.AddItem "Oct"
cboMonth.AddItem "Nov"
cboMonth.AddItem "Dec"
End Sub

Sub Populate_cboYear()
cboYear.AddItem 2000
cboYear.AddItem 2001
cboYear.AddItem 2002
End Sub

Sub GetCustomerID()
Set rst = adoConn.Execute("SELECT Org_ID, OrgName FROM Org WHERE OrgName = '" & cboCustomer & "' &quot
If rst.BOF Then
MsgBox "This customer record not found."
End If
CusID = rst!Org_ID
rst.MoveFirst
End Sub
Private Sub GetRecords()

ADO_Connection

strSQL = "SELECT Call.Org_ID, Call.OpenDate, Call.ClosedDate, " & BegMth & " AS SelectBegMth, " & EndMth & " AS SelectEndMth, " _
& "IIf(Month([OpenDate])= " & BegMth & ",1,0) AS CallsOpened, " _
& "IIf(Month([ClosedDate])= " & BegMth & ",1,0) AS CallsClosed, " _
& "IIf(Month([ClosedDate])> " & BegMth & " Or Month([ClosedDate]) Is Null,1,0) AS CallsInProg " _
& "FROM Call " _
& "WHERE " _
& "(((Call.Org_ID)= '" & CusID & " ') AND " _
& &quot;((Call.OpenDate>=#&quot; & dteBegDate & &quot;#) AND (Call.OpenDate <=#&quot; & dteEndDate & &quot;#)) &quot; _
& &quot;OR &quot; _
& &quot;((Call.Org_ID)='&quot; & CusID & &quot; ') AND &quot; _
& &quot;((Call.OpenDate)<=#&quot; & dteEndDate & &quot;#) AND ((Call.ClosedDate) Is Null Or (Call.ClosedDate)>=#&quot; & dteBegDate & &quot;#));&quot;

'*************************************************************************************************************
'
'TEST ONLY: verify a connection and simple extract - (this completes without errors)
'strSQL = &quot;SELECT * FROM Call&quot;

'CODE CRASHES HERE WHEN RUNNING COLPETE SQL STATEMENT ABOVE - WITH ERROR MSG
'[Microsoft][ODBS SQL Driver][SQL Server] Line 1 Incorrect syntax near '='
'*************************************************************************************************************

Set rstCalls = adoConn.Execute(strSQL)

Do Until rstCalls.EOF

Debug.Print rstCalls(0)
rstCalls.MoveNext
Loop
End Sub

Private Sub ResetCounts()
CallsOpenedCount = 0
CallsClosedCount = 0
CallsInProgCount = 0
End Sub

Private Function GetDaysInThisMonth(dteBegDate)
Select Case Month(dteBegDate)
Case 4, 6, 9, 11
intDaysInThisMonth = &quot;30&quot;
Case 2
If Year(dteBegDate) Mod 4 = 0 Then
intDaysInThisMonth = &quot;29&quot;
Else
intDaysInThisMonth = &quot;28&quot;
End If
Case Else
intDaysInThisMonth = &quot;31&quot;
End Select
End Function

Private Sub GetEndOfMonthDate()
' Get number days in BegDate and set dteEndDate to end of first month (instead of all months in parameter entry)
GetDaysInThisMonth (dteBegDate)
dteEndDate = DateAdd(&quot;d&quot;, intDaysInThisMonth - 1, dteBegDate)
End Sub

Function GetMMM(BegMth)
Select Case BegMth
Case 1
strMMM = &quot;Jan&quot;
Case 2
strMMM = &quot;Feb&quot;
Case 3
strMMM = &quot;Mar&quot;
Case 4
strMMM = &quot;Apr&quot;
Case 5
strMMM = &quot;May&quot;
Case 6
strMMM = &quot;Jun&quot;
Case 7
strMMM = &quot;Jul&quot;
Case 8
strMMM = &quot;Aug&quot;
Case 9
strMMM = &quot;Sep&quot;
Case 10
strMMM = &quot;Oct&quot;
Case 11
strMMM = &quot;Nov&quot;
Case 12
strMMM = &quot;Dec&quot;
End Select
End Function

 

[jonscott8]

Here's my take on it:

1. The first thing (I think) that appears to happen is the Document_Open event fires. It calls RefreshRecordsets which in turn calls a procedure GetAllCustomerRecords which I dont see posted.

2. You are using several variables (BegMth,EndMth,etc..) that I dont see being assigned a value anywhere in your code above.

3. Lastly, since it appears your backend is SQL Server, not Access, replace the # with '. Jon Hawkins