report control with multiple criteria

[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.

 

[John Spencer]

Ouch, you might try the following. I hope I got all the parentheses in the
correct spots.

=Sum(IIf( ( Len([Food_Pantry] &[Clothing] & [Laundry] & [Meal] & [Shower]) >
0
Or ([EducIndiv] Is Not Null And [Topic_PozPrev] Is Null)
Or Len([RefCode1] & [RefCode2] & [RefCode3] & [RefCode4] & [RefCode5]) > 0
Or Len([Program1] & [Program2] & [Program3] & [Program4] & [Program5]) > 0
Or Len([Intervention1] & [Intervention2] & [Intervention3] &
[Interventiion4] & [Intervention5]) > 0)
And [Program_Code] = 60620, 1,0))

Public Function ServiceVisit(CompareCode, ProgramCode, ParamArray
CheckThese()) As Integer
Dim iLoop As Integer

If ProgramCode <> CompareCode Then
ServiceVisit = 0
Exit Function
Else

For iLoop = LBound(CheckThese) To UBound(CheckThese)
If IsNull(CheckThese(iLoop)) = False Then
ServiceVisit = 1
Exit For
End If
Next iLoop
End Function


Call the above is this form.
= Sum(ServiceVisit(60620, Program_Code,
[Food_Pantry],[Clothing],[Laundry],...,[Intervention4],[Intervention5]))

I see one problem with the above code, it will not correctly check this
condition.
[EducIndiv] Is Not Null And [Topic_PozPrev] Is Null)

I included CompareCode in the function to allow you to use this same bit of
code with other Program_Codes. You just tell it which Value you want to
compare against which field and then enter all the fields you want to check.

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.

 

[kmilberg]

Thank you so much!!!

So now, i got this code to work (I needed to add another End If to the
code that you sent), and tried to add additional lines to make it look
for [EducIndiv] Is Not Null And [Topic_PozPrev] Is Null. See below...
(the control source is then:


=Sum(ServiceVisit1(60620,[Program_Code],[EducIndiv],[Topic_PozPrev],[Food_Pantry],[Clothing],[Laundry],[Meal],[Shower],[RefCode1],[RefCode2],[RefCode3],[RefCode4],[RefCode5],[Program1],[Program2],[Program3],[Program4],[Program5],[Intervention1],[Intervention2],[Intervention3],[Interventiion4],[Intervention5]))

but i'm ending up with the same problem that i had originally with the
report just reverting to design view when i try to run it... no error
message... any thoughts?

Public Function ServiceVisit1(CompareCode As Integer, ProgramCode As
Integer, _
IndEduc As String, Topic As String, ParamArray CheckThese())
Dim iLoop As Integer


If ProgramCode <> CompareCode Then
ServiceVisit1 = 0
Exit Function
Else

For iLoop = LBound(CheckThese) To UBound(CheckThese)
If IsNull(CheckThese(iLoop)) = False Then
ServiceVisit1 = 1
Exit For
End If
Next iLoop

If ServiceVisit1 = 0 Then
If IsNull(IndEduc) = False Then
If IsNull(Topic) = True Then
ServiceVisit1 = 1
End If
End If
End If

End If
End Function