devon59
01-13-2009, 10:23 AM
Hi Everyone, I am new to VBA and I don't know how to fix this code. Right now, in the code below, it only allows to me input only one Phase at a time. If I want to have multiple Phase, how do I change the code below? Phase field is stored in the server as numeric value. I tried mimicing what I did for Project field but Project was stored as Text so it didn't work for Phase field. Please help. Thank you..
*******************************************************
Function sfResp(Optional client As String, Optional Project As String, Optional Phase As Integer, _
Optional StartDate As Date, Optional EndDate As Date) As Long
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim rsReceiptDate As ADODB.Recordset
StagingConnection
Set rs = New ADODB.Recordset
sSQL = "SELECT SUM(CASE WHEN DonationAmount > 0 THEN 1 ELSE 0 END) AS Responses," & vbCrLf
sSQL = sSQL & " SUM(DonationAmount) As Revenue" & vbCrLf
sSQL = sSQL & "FROM dbo.tblresponse R" & vbCrLf
sSQL = sSQL & "WHERE R.ClientID in ('" & client & "') " & vbCrLf
If Len(Project) > 0 Then
sSQL = sSQL & " AND R.Project in ('" & Project & "') " & vbCrLf
End If
If Len(Phase) > 0 Then
sSQL = sSQL & " AND R.Phase = " & Phase & vbCrLf
End If
rs.Open sSQL, cnStaging
' sSQL = "SELECT MAX(ReceiptDate) AS ReceiptDate" & vbCrLf
' sSQL = sSQL & "FROM VFW.dbo.tblProjectResponse" & vbCrLf
' sSQL = sSQL & "WHERE Project IN (" & Project & ")" & vbCrLf
' Set rsReceiptDate = New ADODB.Recordset
' rsReceiptDate.Open sSQL, cnStaging
If IsNull(rs!Responses) Then
sfResp = 0
Else
'If rsReceiptDate!ReceiptDate < EndDate Then
' Responses = rs!Responses / WeekFactor(rs!MaxReceiptDate)
'Else
sfResp = rs!Responses
'End If
End If
Set rs = Nothing
End Function
*******************************************************
Function sfResp(Optional client As String, Optional Project As String, Optional Phase As Integer, _
Optional StartDate As Date, Optional EndDate As Date) As Long
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim rsReceiptDate As ADODB.Recordset
StagingConnection
Set rs = New ADODB.Recordset
sSQL = "SELECT SUM(CASE WHEN DonationAmount > 0 THEN 1 ELSE 0 END) AS Responses," & vbCrLf
sSQL = sSQL & " SUM(DonationAmount) As Revenue" & vbCrLf
sSQL = sSQL & "FROM dbo.tblresponse R" & vbCrLf
sSQL = sSQL & "WHERE R.ClientID in ('" & client & "') " & vbCrLf
If Len(Project) > 0 Then
sSQL = sSQL & " AND R.Project in ('" & Project & "') " & vbCrLf
End If
If Len(Phase) > 0 Then
sSQL = sSQL & " AND R.Phase = " & Phase & vbCrLf
End If
rs.Open sSQL, cnStaging
' sSQL = "SELECT MAX(ReceiptDate) AS ReceiptDate" & vbCrLf
' sSQL = sSQL & "FROM VFW.dbo.tblProjectResponse" & vbCrLf
' sSQL = sSQL & "WHERE Project IN (" & Project & ")" & vbCrLf
' Set rsReceiptDate = New ADODB.Recordset
' rsReceiptDate.Open sSQL, cnStaging
If IsNull(rs!Responses) Then
sfResp = 0
Else
'If rsReceiptDate!ReceiptDate < EndDate Then
' Responses = rs!Responses / WeekFactor(rs!MaxReceiptDate)
'Else
sfResp = rs!Responses
'End If
End If
Set rs = Nothing
End Function