calculated control in report header too long

[kmilberg]

I am a relatively new user to Access and I am using Access 2000. I am
using the report and group headers to calculate totals for the report
and for the most part it is working well. However, I have some
aggregate functions in the controls that appear to be too long and
cause the report to revert directly back to the design veiw when it is
run. For example:

=IIf(Sum((([RefCode1]>="110" And [RefCode1]<="119") Or
([RefCode2]>="110" And [RefCode2]<="119") Or ([RefCode3]>="110" And
[RefCode3]<="119") Or ([RefCode4]>="110" And [RefCode4]<="119") Or
([RefCode5]>="110" And [RefCode5]<="119")),1,0))

will cause the report to not be able to run (i get no error message,
but the report just reverts back to design veiw), but if i take out one
of the agruements (it doesn't matter which one)... so for example:

=IIf(Sum((([RefCode1]>="110" And [RefCode1]<="119") Or
([RefCode2]>="110" And [RefCode2]<="119") Or ([RefCode3]>="110" And
[RefCode3]<="119") Or ([RefCode4]>="110" And [RefCode4]<="119")),1,0))

this one works just fine.

in addtion, in the detail of the report the full expression which is
for a check box also works just fine:

=IIf((([RefCode1]>="110" And [RefCode1]<="119") Or ([RefCode2]>="110"
And [RefCode2]<="119") Or ([RefCode3]>="110" And [RefCode3]<="119") Or
([RefCode4]>="110" And [RefCode4]<="119") Or ([RefCode5]>="110" And
[RefCode5]<="119")),True,False)

I have been at this for days trying to figure out different ways around
this, but just can't seem to figure it out. the expression just doesn't
seem to be that long and i'm unsure how to get this to work.

please help if you can.

thanks!

 

[Duane Hookom]

I would write a user defined function that accepts arguments of your 5
fields and returns the appropriate value. This would move your "business
rule" to a single place where it can more easily be maintained. I try to
never hard-code values like 119, 110,... into expressions.

I also would question if the field value is text or numeric and why you have
5 similar fields.

 

[kmilberg]

Since I am really new to this, I have no idea how to write a user
defined function or event what this is -- I think that I've tried to
figure this out, but to no avail. can you help with this, or suggest a
resource that might be useful?

In terms of why we have five similar fields, first, I inherited this
database and it gets its data from forms that are electronically
scanned by I system that I don't have any control over -- So I don't
think that I have much flexibility to change it around. These five
different fields represent referral codes (representing the type of
referral made) so for any given meeting with a client, up to five
different referrals can be made and the referral codes are on the form
which is then scanned. The field is a text field (again, I have no
idea why).

thanks for the response!

 

[Duane Hookom]

Can you describe what you expression accomplishes? It looks like you are
attempting to count the number of referrals where any one of the ref codes
is between 110 and 119. I'm not sure why the IIf() is on the outside of the
Sum().

 

[kmilberg]

Sorry, you're right, sum should be on the outside (just a typing
mistake), but this doesn't make it work. and, yes, I am trying to
count any record where a referral is made to codes 110-119. i've tried
to use the count function in similar way, but this doesn't work either.
however, one interesting fact of note is that i have tried this code
on a computer with Access 2003 and it works... but on another computer
with Access 2003 it does not work... and on my computer with Access
2000 it does not work.

 

[Duane Hookom]

To find out if a record has 110-119 in any record, you can create a function
like below in a standard modulte and save it with the name
"basBusinessCalcs". You can use this function in your query like:

Is110_119:
GetRefCount([RefCode1],[RefCode2],[RefCode3],[RefCode4],[RefCode5])

You should be able to use this in a group footer to "count" records that
meet the criteria:

=Sum(GetRefCount([RefCode1],[RefCode2],[RefCode3],[RefCode4],[RefCode5]))

Function GetRefCount(varCode1 As Variant, varCode2 As Variant, _
varCode3 As Variant, varCode4 As Variant, _
varCode5 As Variant) As Integer
Dim strAllCodes As String
Dim intCode As Integer
Dim intReturn As Integer
intReturn = 0
strAllCodes = "," & varCode1 & "," & varCode2 & "," & _
varCode3 & "," & varCode4 & "," & varCode5 & ","
For intCode = 110 To 119
If InStr(strAllCodes, "," & intCode & ",") > 0 Then
intReturn = 1 'count it
End If
Next
GetRefCount = intReturn
End Function

 

[kmilberg]

okay... so i think that i finally got it. what i realized that i also
need to do is to specify additional criteria: [Program_Code]=60200 And
[La Voz SAMHSA -- Outreach Only Participants.ClientID] Is Not Null is
there a place that i can put this into the code that you sent?

 

[Duane Hookom]

Can you please start over with your complete specifications/requirements? I
don't know what these criteria have to do with others.

 

[kmilberg]

so i have different calculated controls that all need to have the
original requirements that i posted (records need to be counted for
anyone receiving a referral with codes 110-119). then, for some of the
controls there are additional requirements, for example one control
needs only include records where [Program_Code]=60200 And [La Voz
SAMHSA -- Outreach Only Participants.ClientID] Is Not Null another
control needs to include the original referral code requirements and
also only those where [Program_Code]=60620, etc. i tried to add the
additional requirements to:


=Sum(GetRefCount([RefCode1],[RefCode2],[RefCode3],[RefCode4],[RefCode5]))


so, for example: =IIf([Program_Code] = 60620,
Sum(GetRefCount([RefCode1],[RefCode2],[RefCode3],[RefCode4],[RefCode5])),0)


but it didn't seem to work. I have never done work in Visual Basic
before and I think that I'm just missing something... Sorry for being
just so confused.

 

[kmilberg]

Okay -- I figured it out! I put the IIf function inside of the Sum
function: =Sum(IIf([Program_Code] = 60620, GetRefCount([RefCode1],
[RefCode2], [RefCode3], [RefCode4], [RefCode5]), 0))

 

[Duane Hookom]

It looks like you again put the Sum() inside when I think it goes outside.
To count records where GetRefCount() and Program_Code = 60620, try (assuming
Program_Code is numeric):

=Sum(Abs([Program_Code] = 60620) *
GetRefCount([RefCode1],[RefCode2],[RefCode3],[RefCode4],[RefCode5]))