Group totals in dynamic report

I have a report that dynamically gets control source for five columns based 
on user input through a form. The report successfully shows text boxes 
(currency format) with approporiate fields (control source) and respective 
data. I then have unbound text boxes for each of the five columns to show 
group totals. I haven't been able to get it working. For example, one of the 
text boxes' name is txtPeriod1 and based on user selection the field is 
MAY_PYR. The group total should show Sum(MAY_PYR); it works through hard 
coding but not dynamically and keeps prompting to enter "txtPeriod1" 
parameter. Please suggest solution. Thanks in advance!
0
Utf
4/26/2010 8:05:01 PM
access.reports 4434 articles. 0 followers. Follow

3 Replies
1605 Views

Similar Articles

[PageSpeed] 19

Not enough information...
Are the dynamic columns derived based on months?
Why are they dynamic?
How do you get and set the record source of the report?

-- 
Duane Hookom
Microsoft Access MVP


"mrashid" wrote:

> I have a report that dynamically gets control source for five columns based 
> on user input through a form. The report successfully shows text boxes 
> (currency format) with approporiate fields (control source) and respective 
> data. I then have unbound text boxes for each of the five columns to show 
> group totals. I haven't been able to get it working. For example, one of the 
> text boxes' name is txtPeriod1 and based on user selection the field is 
> MAY_PYR. The group total should show Sum(MAY_PYR); it works through hard 
> coding but not dynamically and keeps prompting to enter "txtPeriod1" 
> parameter. Please suggest solution. Thanks in advance!
0
Utf
4/26/2010 9:45:01 PM
Presumably you are using Report_Open to assign the ControlSource of the text 
boxes, based on the actual field names.

You can also assign the ControlSource of the total box in the group footer, 
e.g.:
    Me.Total0.ControlSource = "=Sum([Field99])"

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"mrashid" <mrashid@discussions.microsoft.com> wrote in message 
news:9A72E7FA-8E52-46F3-B28A-1C49E7C52DD4@microsoft.com...
> I have a report that dynamically gets control source for five columns 
> based
> on user input through a form. The report successfully shows text boxes
> (currency format) with approporiate fields (control source) and respective
> data. I then have unbound text boxes for each of the five columns to show
> group totals. I haven't been able to get it working. For example, one of 
> the
> text boxes' name is txtPeriod1 and based on user selection the field is
> MAY_PYR. The group total should show Sum(MAY_PYR); it works through hard
> coding but not dynamically and keeps prompting to enter "txtPeriod1"
> parameter. Please suggest solution. Thanks in advance! 

0
Allen
4/27/2010 3:43:42 AM
Thanks folks. I actually got it working using Domain Sum. Sorry for delayed 
reply.

Thanks again!

"Allen Browne" wrote:

> Presumably you are using Report_Open to assign the ControlSource of the text 
> boxes, based on the actual field names.
> 
> You can also assign the ControlSource of the total box in the group footer, 
> e.g.:
>     Me.Total0.ControlSource = "=Sum([Field99])"
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> 
> "mrashid" <mrashid@discussions.microsoft.com> wrote in message 
> news:9A72E7FA-8E52-46F3-B28A-1C49E7C52DD4@microsoft.com...
> > I have a report that dynamically gets control source for five columns 
> > based
> > on user input through a form. The report successfully shows text boxes
> > (currency format) with approporiate fields (control source) and respective
> > data. I then have unbound text boxes for each of the five columns to show
> > group totals. I haven't been able to get it working. For example, one of 
> > the
> > text boxes' name is txtPeriod1 and based on user selection the field is
> > MAY_PYR. The group total should show Sum(MAY_PYR); it works through hard
> > coding but not dynamically and keeps prompting to enter "txtPeriod1"
> > parameter. Please suggest solution. Thanks in advance! 
> 
> .
> 
0
Utf
6/7/2010 9:34:24 PM
Reply:

Similar Artilces:

Totaling Multiple Subreports
Hi, I have a main report with four subreports and three of those have subreports on them. All are linked by salesperson and I need a grand total of each department from subreport1, subreportof2, subreportof3 and subreportof4. I'm not sure how to go about this as everything I've tried has not worked - name error, same total repeated for each department, etc. The fields on each summary report in the department group are deptname and deptsumgp07, deptsumgp08 and deptsumgp09. For clarity (I hope), I 've liste the following setup: Main report: Groups...

Exception Reports
Does anyone know if it is possible to complete the following query using standard MSCRM querying tools i.e. standard reports / advanced find Query the database to view all of the accounts that have not had any activities created or updated within the past x months? Appreciate I can create a custom report to do this, but is it possible using the standard tools? Thanks Jo Savidge i tihnk its possible but you will always have issues..what happens if someone added an actvity on a contact linked to the account for example. If you have a process that says users can only add activities to acc...

Grouping data
I am trying to group data for a specific event and have not been successful, so I am hoping someone can help. ID Date Value 174 12/3/2007 102 174 12/3/2007 909 174 12/11/2007 405 174 12/11/2007 701 206 12/6/2007 1001 173 12/3/2007 500 I want to group by ID, the MAX Date for that ID and the Max Value for the ID & Max Date The desired result would be: ID Date Value 174 12/11/2007 701 206 12/6/...

Group Footer at Bottom of Last Page of Group
I would like for a group footer to appear at the bottom of the last page of a group -- either hide the page footer on pages if it is not the last page of the group or have the group footer print at the bottom of the page. I have used the instructions to reset the page number and the total page count for each group. (http://support.microsoft.com/kb/841779/en-us) I was hoping to be able to set it where if the page equals the total pages of the group, it will make the page footer visible. For some reason, if there is more than one page, it doesn't work properly. I have also used the i...

reporting even though a calculation made is dividing by zero
I have a report which initially opens a form to input a start and end date in for the report information. One of the calculated reports items is a % of bad rolls produced. My report opens; I input the dates and it then runs a query based on those dates. It takes the data (put into another form) with fields of "total rolls produced" and "off quality rolls" and divides the "off quality" by the "total" to give a % bad value. The report works great IF the "total rolls produced" is not zero. If we do not produce any rolls that day (due...

query based distribution group
Hello all running Exchange 2007 sp2, I need to modify an existing query based DL so it finds mailboxes and if the account has two different email address stamped on it. For example if the mailbox has test1.com and test2.com and it's a mailbox then make it a member of the query based DL Thanks for any help If it has those two e-mail addresses? That should return just one recipient. Or do you mean a recipient with any two proxy addresses? I don't believe that's possible with LDAP. Or do you mean a mailbox-enabled user with proxy addresses in two domai...

Creating Universal Groups with Distribution Group members?
Hello all. We have a Windows 2003 Active Directory with Exchange 2003, and I don't really want to duplicate work, but I think I may have to (ugh!). Here is the deal. I need to create an All Managers Universal Group with every group that has managers in it. So, what I tried doing, was I created a Universal Group and added the _All Managers DL into it -- but that didn't seem to work, according to my boss who asked me to create this UG for him. So, my question is, am I not allowed to add DL members to a Universal Security Group? And if now, is there any way that I can take...

Can't get report to show using 2003 MDE with DBwindow hidden
My database is written in 2003, is used with runtime and an MDE file, and I hide the database with the checkbox in 'Startup' and use 'Application.RunCommand acCmdAppMinimize' so the users see only the windows I provide. I run the report and nothing shows. I assume it is trapped in the DB window. Is there a way to show it without maxing the DBwindow and minimizing it again on the close of the report? Thanks, Yes, for reports you must maximize the application (DoCmd.RunCommand acCmdAppMaximize) before opening the report. Then, you can use the Minimize code again in t...

Using Password-protected files for a report
I have many user files (timesheets) and a report file (payroll report) that uses these timesheets as links. The timesheets are password-protected. I need the report to be able to be updated without opening all the files. Is this possible? Thanks! ...

Distribution Group list
I have a problem with the distribution group list. Here it goes: I removed and deleted the account from this group list but whenever I send a task, it always tries to reach this deleted account and give me a undeliverable error message. Please help. Thanks Restart Exchange services. Linh wrote: > I have a problem with the distribution group list. Here > it goes: I removed and deleted the account from this > group list but whenever I send a task, it always tries to > reach this deleted account and give me a undeliverable > error message. Please help. Thanks ...

Data, Group got error message Cannot shift object off sheet
I created a command grouping several columns together by going to Data, Group. When I go to click on the "-" sign to group. It gives me an error message;"Cannot shift object off sheet". Why? and how can I fix it. Maybe you have some objects that get rearranged when you hide the columns using your grouping symbols. This may have some suggestions that work for you. It's kind of a similar problem. XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769 Remember to look for comments and merged ...

Local Data Groups
Hi, Does anyone know if its possible to create local data groups for offline clients and somehow push the groups out to all users, rather than going to each outlook laptop client? Jo Savidge You can deploy the SFO client using group policies. When the user next logs onto the domain the SFO client will be installed ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm http://www.crowechizek.com/microsoft "Jo Savidge" <JoSavidge@discussions.microsoft.com> wrote in message news:CAE65DA4-C2E3-4F50-A78B-39C98940A129@microsoft.c...

graphing by group
I have 10 individuals in 5 groups. I would like to graph the individuals but keeping them within their groups. When I try to graph it ends up as 10 groups. I would also like to make each group of individuals a different colour. Thanks, Tim Hi Tim In Step 2 of Chart Wizard make sure you have Series by Rows or Series by Columns correctly selected. If you groups are in different columns us Series by Columns other wise use Series by Rows -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "Tim Pasma" <anonymous@discussions.microsoft.com> wrote in m...

Passing option group value to vba function
I'm sure I've done this before but can't for the life of me figure it out now, and hours of research online haven't helped. I'm simply trying to pass an option group value from a form to a vba function. I'd like the user to choose option1 or option2 and when they click a button, the function will run an if statement like "if option1.value=true then...." I've got 2 options named option1 and option2 and a frame called frame20, what else do I need? I've also tried bounding the selection to a textbox, but keep getting errors. Any help on the process? Tha...

DBMS: 8114 Microsoft Dynamics GP: 8114
hi, When I am trying to post entry from Fixed Assets to GL the following errors will occur in Microsoft GP 10.0 "The stored Procedure aagUpdateBrowseTypeForBRVoiedTRX returned the following results: DBMS:8114, Microsoft Dynamics GP: 8114." Kindly help me out to resolve this problem.. Imran If the error text refers to both DBTYPE_DATE and datetime, respectively, this error message indicates that a DBTYPE_DATE OLE DB data type column from a remote table could not be converted to a datetime value in Microsoft SQL Server. The DBTYPE_DATE column most likely has a value outside the r...

managing distribution groups
Hello all, I am running exchange 2003 and I would like to know is there a way and I can have a regular user maintain my distribtion groups. If so how would I go about that? Thanks so much Mike You can grant permissions at the disty group level to allow ordinary members to add and remove users. Be careful to only do disty groups as security groups changed by users in outlook is a touch insecure. "Mike B" <MikeB@discussions.microsoft.com> wrote in message news:0F2F3709-0900-46B1-B87B-A2449FD44B75@microsoft.com... > Hello all, > > I am running exchange 2003 and...

Reports Caching??
I've just created a few reports and it appears that they may be caching during the open CRM session. Here's what I did: Added the new report to CRM. Run it and closed it. Made changes to an Account that showed up in the report and save changes. Went back and re-ran the report, and the Account did not update. If I exit out of CRM and then log back in and open run the report, the updated account changes display. Is this correct funcationality?? James ...

DSum by group
I have a textbox with a DSum function, but would like it to query only within each group. It seems that most other functions (e.g Avg) automatically calculate by group, but DSum doesn't seem to be. =DSum([MKTCAP],"QryFILTER",[MKTCAP]>100) The text box is within the Group Footer, and I'd like it to return a different value below each group. Thanks! "RussG" <RussG@discussions.microsoft.com> wrote in message news:697878DC-521A-4423-BB7F-97089D536EF2@microsoft.com... >I have a textbox with a DSum function, but would like it to query only >within...

Problem with data sheet and linking it to another sheet to make a report
This is kind of hard to explain, but I will try. I'm making a report in excel by linking the report sheet to another sheet that has the data. What I am doing is entering the equal sign, and then clicking on the data in sheet 2 and put them under the labels I have on sheet 1 (the report). So here are the steps so far: 1. I open excel and design a report with lables and field names, now i just need the data for the report 2. I insert a new sheet that has the data. 3. I go back to the first sheet (report) and put an equal sign into one of the fields where I want the data to appear in my r...

Help Totaling More Than One Entry
I've posted this before but now can't locate my post. I have a membership database in which I have a form created for input of general data on each member (address, phone, email, etc) that is posted to the Members table. Members make contributions and some members make more than one throughout the year. I also have a Contributions table that shows each Member's ID #, the date of the contribution, the reason, and the amount. I created a subform of the Contributions form and imbedded it into the Members form so that when I enter Member info I can also enter their Contr...

reporting mailbox delegation ACLs using Powershell
Does anyone know of a way to retrieve the ACLs set on 2007 mailbox folders using Powershell? Say for example, user Joe has granted reviewer rights on his Inbox to Carla - I'd like to use Powershell to find and display that information. I've tried using the get-mailboxpermission and get-adpermission cmdlets but haven't found a way of retrieving them. Any help gratefully received... All the best, Darren ...

Re: Protecting worksheet with grouped cells
I have a worksheet which has rows as well as cloumns grouped. After protecting the worksheet I am unable to expand and collaspe my groups. error:"You cannot use this command on a protected sheet..." Is there a work-around/solution to this problem? You can only enable outlines and have worksheet protection through a macro: With Worksheets("Sheet1") .EnableOutlining = True .Protect UserInterfaceOnly:=True End With The EnableOutlining property (and UserInterfaceOnly) is not saved with the workbook unfortunately, so this code would have to be run ...

Grouping and grouping within groups
I really need help, and help fast, so thank you ahead of time! I nee to know how to group and group within a group. I have been able to d a group, but once I group within the group and try to group anothe group withing that group it combines them. I know I can put a spac between the information so it doesn't combine them but that doesn' seem to be the right way to do things. For instance I have thre different area codes and I want to group them, then within those group I have five different colors of houses in that area code. I want t group the different colors within their area ...

Dynamically Hiding Empty Series with VBA?
My application generates a chart using VBA that defines the various column ranges and uses Union to sum them for .SetSourceData Sometimes a aparticular series has no valid data - but always has a header. How can I prevent that series from showing up in the chart AND its header showing up in the legend? One idea was to hide the EntireColumn after the chart is built but I cannot seem to find a way of determining whether a column range is empty of numeric data since they all have a header. Any ideas how to tackle this? Thanks Bill If you want to check if a particular column has only a ...

Show Groups as Page Header
I used the report wizard to create a report based on a table. The table has 252 records. When I preview the report, it only shows 242 records. The records are sorted / grouped by district. In the table, the fields are District Business Name DBA Name Amount The wizard placed the District and field names into the District Name Header. To make the report more readable, how can I get the group headers to be the page headers? That is, if a group prints over more than one page, how can I get the page header to show the group name? You can set the "Repeat Section" property of the Gro...