VBA Coding for function that has multiple Integer Value

[devon59]

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

[Kieran]

Devon59,

The defintion of the Phase parameter in the function sfResp is Integer. This will not allow for the passing of multiple values to the function.

If the complete list of phases required is packed into a comma separated string (before the sfResp function is called) , and the parameter type chagned to string, then I think the syntax sSQL = sSQL & " AND R.Project in ('" & Phase& "') " & vbCrLf will work.

You could achieve a similar result by passing an array of Phase integers to the function, but you would still have to parse this array for inclusion in the SQL string.