Sum formula help please & thank you

[travismp]

Access 2000

I have a report right now that works fine. I have a field that sums all records

=Sum([REASON COUNT])

There is also a field [CompName]. I want to add three new fields that will give me that same sum but for three sections of the alphabet.

So my report will have:
Total Number of Records - 100
Total for Companies that start A-I - 40
Total for Companies that start J-R - 25
Total for Companies that start S-Z - 35


How would I make a the new formulas?

Thanks.

 

[stephanieleshk]

Hello. Sounds like you're gonna have to write some VB code. Try something like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If [Companies] between A and I then text81=sum[companies]
If [Companies] between j and r then text82=sum[companies]
ect...

End Sub


where text81/text82 are unbound textboxes.

Hope this helps!
Stephanie

 

[pbaldy]

Try

=Sum(IIf(Left([CompName],1) Between "a" And "i",[REASON COUNT],0))

 

[boblarson]

Why not just create the company breakouts in the underlying query and that way you can just have individual text boxes with =SUM([CompanyAI]), =SUM[CompanyJR]) etc.

 

[travismp]

Try

=Sum(IIf(Left([CompName],1) Between "a" And "i",[REASON COUNT],0))

That worked PERFECT! Thanks for that. The only real change is what about companies that start with a number? I have "3M Construction" and similar names. Can numbers be added to the first section?

Thanks!

 

[travismp]

Actually I think this might work for the numbers.

=Sum(IIf(Left([CompName],1)<"j",[REASON COUNT],0))

 

[travismp]

Why not just create the company breakouts in the underlying query and that way you can just have individual text boxes with =SUM([CompanyAI]), =SUM[CompanyJR]) etc.

I use the underlining query for a couple different reports, but this one needed a coversheet with this info on it.

 

[boblarson]

Good reason not to alter the query then.