VB code

[bkast3]

I have an access db report that displays a several fields based on the data in one field. The field is total time. it is based on the description of a service provided. If the service has a T* then list it as "Team" hours if it has a I* list it as "individual" hours and if it is neither of these 2 then list is as "General" hours. This works OK, but I did not create the report. So I now want to add the percentage of time that is spent between only the team and individual hours excluding the general hours and I cant find where the code writes to sort these two times. Any suggestions. The db is rather small right now and I could email it to someone if needed to help me out. I am facing a deadline and cant seem to figure it out. I know it has to be fairly simple because the times are displaying correctly.
Thanks.

 

[fredk]

I am not sure I understand you completely. You could add the field that holds the T,I,G to the report and then it will give you totals for each category.

In other words, your report would have category (the field with the T,I G) and then sum the hours and do any calculations.

Fred

 

[fredk]

Just let me clarify, you would actually add the field that holds the T,I,G to the query that is the source of the report.

By adding this field to the query, it will sum each category - You could also add <> G in the criteria to exclude the general.

Hope that helps.

Fred

 

[snyperx3]

Shouldnt the fields that hold T I and G already be in the query if he is using them in the report? He said they are showing up correctly.

bkast3: my thoughts would be to go to the report footer and make an unbound textbox to make the calculation. If there is an Hours field to coorespond with the Service field in each record you could get the total hours for each by using something like this:

This is for Team hours
=Sum(IIf([Status]="T",[Hours],0))
...or i guess it would be
=Sum(IIf([Status]="T*",[Hours],0))

So if all this is correct so far, you could make an unbound textbox in the report footer to contain this.

=(Sum(IIF([Status]="T*",[Hours],0)) + Sum(IIf([Status]="I*",[Hours],0))) / Sum([Hours])
Thats total team hours plus total individual hours divided by the total overall hours

You could further extend this by either formatting the cell, or using this:
=CStr(((Sum(IIF([Status]="T*",[Hours],0)) + Sum(IIf([Status]="I*",[Hours],0))) / Sum([Hours]))*100) & "%"

-Pete

 

[fredk]

Not sure how you can say that the field is in the query since he did not say that. How do you know that he is not creating the column with the T,I or G in the report just like you are suggesting to code the report?

I am not saying that your solution won't work. I would just think it to be more efficient to get the results in the query rather than adding all that code to the report. If that is not possible, then I would say to take your approach.

Fred