K
kmilberg
I'm trying to finish up some reports, but have some controls where
there are too many criteria which causes the report to "crash" and
revert to design mode. I got some help -- someone provided me with
some visual basic code -- with one of the control where criteria were
only numeric values. I have tried to change this one around to include
text criteria, but it's not working. here is what the expression would
look like in the control:
=Sum(IIf(([Food_Pantry] Is Not Null Or [Clothing] Is Not Null Or
[Laundry] Is Not Null Or [Meal] Is Not Null Or [Shower] Is Not Null Or
([EducIndiv] Is Not Null And [Topic_PozPrev] Is Null) Or [RefCode1] Is
Not Null Or [RefCode2] Is Not Null Or [RefCode3] Is Not Null Or
[RefCode4] Is Not Null Or [RefCode5] Is Not Null Or [Program1] Is Not
Null Or [Program2] Is Not Null Or [Program3] Is Not Null Or [Program4]
Is Not Null Or [Program5] Is Not Null Or [Intervention1] Is Not Null Or
[Intervention2] Is Not Null Or [Intervention3] Is Not Null Or
[Interventiion4] Is Not Null Or [Intervention5] Is Not Null) And
[Program_Code] = 60620, 1,0))
basically, if any of the above field are null AND [Program_Code] =
60620, then i want the record to be counted.
this is what i have come up with for Visual Basic code -- but, i really
don't understand how to do this coding, so i'm probably way off...
Function GetServiceVisitCount(varCode1 As Variant, varCode2 As Variant,
_
varCode3 As Variant, varCode4 As Variant, _
varCode5 As Variant, varCode6 As Variant, _
varCode7 As Variant, varCode8 As Variant, _
varCode9 As Variant, varCode10 As Variant, _
varCode11 As Variant, varCode12 As Variant, _
varCode13 As Variant, varCode14 As Variant, _
varCode15 As Variant, varCode16 As Variant, _
varCode17 As Variant, varCode18 As Variant, _
varCode19 As Variant, varCode20 As Variant) As Integer
Dim strAllCodes As String
Dim intReturn As Integer
Dim NullVar As Boolean
intReturn = 0
strAllCodes = "," & varCode1 & "," & varCode2 & "," & _
varCode3 & "," & varCode4 & "," & varCode5 & "," & _
varCode6 & "," & varCode7 & "," & varCode8 & "," & _
varCode9 & "," & varCode10 & "," & varCode11 & "," & _
varCode12 & "," & varCode13 & "," & varCode14 & "," & _
varCode15 & "," & varCode16 & "," & varCode17 & "," & _
varCode18 & "," & varCode19 & "," & varCode20 & ","
If IsNull(NullVar) = False Then
If InStr(strAllCodes, "," & NullVar & ",") > 0 Then
intReturn = 1 'count it
End If
End If
GetServiceVisitCount = intReturn
End Function
any help would be greatly appreciated... or suggestions of any
resources about visual basic.
there are too many criteria which causes the report to "crash" and
revert to design mode. I got some help -- someone provided me with
some visual basic code -- with one of the control where criteria were
only numeric values. I have tried to change this one around to include
text criteria, but it's not working. here is what the expression would
look like in the control:
=Sum(IIf(([Food_Pantry] Is Not Null Or [Clothing] Is Not Null Or
[Laundry] Is Not Null Or [Meal] Is Not Null Or [Shower] Is Not Null Or
([EducIndiv] Is Not Null And [Topic_PozPrev] Is Null) Or [RefCode1] Is
Not Null Or [RefCode2] Is Not Null Or [RefCode3] Is Not Null Or
[RefCode4] Is Not Null Or [RefCode5] Is Not Null Or [Program1] Is Not
Null Or [Program2] Is Not Null Or [Program3] Is Not Null Or [Program4]
Is Not Null Or [Program5] Is Not Null Or [Intervention1] Is Not Null Or
[Intervention2] Is Not Null Or [Intervention3] Is Not Null Or
[Interventiion4] Is Not Null Or [Intervention5] Is Not Null) And
[Program_Code] = 60620, 1,0))
basically, if any of the above field are null AND [Program_Code] =
60620, then i want the record to be counted.
this is what i have come up with for Visual Basic code -- but, i really
don't understand how to do this coding, so i'm probably way off...
Function GetServiceVisitCount(varCode1 As Variant, varCode2 As Variant,
_
varCode3 As Variant, varCode4 As Variant, _
varCode5 As Variant, varCode6 As Variant, _
varCode7 As Variant, varCode8 As Variant, _
varCode9 As Variant, varCode10 As Variant, _
varCode11 As Variant, varCode12 As Variant, _
varCode13 As Variant, varCode14 As Variant, _
varCode15 As Variant, varCode16 As Variant, _
varCode17 As Variant, varCode18 As Variant, _
varCode19 As Variant, varCode20 As Variant) As Integer
Dim strAllCodes As String
Dim intReturn As Integer
Dim NullVar As Boolean
intReturn = 0
strAllCodes = "," & varCode1 & "," & varCode2 & "," & _
varCode3 & "," & varCode4 & "," & varCode5 & "," & _
varCode6 & "," & varCode7 & "," & varCode8 & "," & _
varCode9 & "," & varCode10 & "," & varCode11 & "," & _
varCode12 & "," & varCode13 & "," & varCode14 & "," & _
varCode15 & "," & varCode16 & "," & varCode17 & "," & _
varCode18 & "," & varCode19 & "," & varCode20 & ","
If IsNull(NullVar) = False Then
If InStr(strAllCodes, "," & NullVar & ",") > 0 Then
intReturn = 1 'count it
End If
End If
GetServiceVisitCount = intReturn
End Function
any help would be greatly appreciated... or suggestions of any
resources about visual basic.