 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / December 2006ms access Text Box help
I have a text box located in the requred footer section of an Access report. The formula is: =Sum([Estimated Hour Required]) How can I calculate this field in increments of 2500? Basically, if one item on the first page has an Estimated Hour Required that equals 2500, I would like the page to reflect that one item, total that one item, and start a new page to begin calculating the next sum of 2500. Hi spud, here is something to try: =Sum([Estimated Hour Required]) MOD 2500 this gets the remainder... =Sum([Estimated Hour Required]) \ 2500 should get the number of 2500's ... Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * > I have a text box located in the requred footer section of an Access > report. The formula is: =Sum([Estimated Hour Required]) How can I [quoted text clipped - 3 lines] > that one item, and start a new page to begin calculating the next sum > of 2500. Hi Crystal, Thank you for responding... Your formula does calculate the 2500. However, once it reaches the 2500, I need to start a new page. Any "iif statement" ideas? Regards, Spud > Hi spud, > [quoted text clipped - 24 lines] > > that one item, and start a new page to begin calculating the next sum > > of 2500. Hi Spud, one way is to put the calculations in the report RecordSource and group on something (the grouped field can easily change pages)... how is the report sorted? Do you use a report filter with the OpenReport action, or how do you specify criteria? Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * > Hi Crystal, > [quoted text clipped - 32 lines] >>> that one item, and start a new page to begin calculating the next sum >>> of 2500. Hi Crystal, The record source for this field is an entry made by the user. It is being entered into a table which indicates the number of hours it will take to complete a particular function. Each record that is entered does have a unique ID. However, I would like each record to be listed on the report. There is no grouping order. I would like the report to start a new page once it reaches the threshold of 2500 hours. I tried placing the code, illustrated below, into the detail - on format - command of the report. This code seems to work for the 1st page but page 2 will only shows a single record and so-on and so-forth. I would like my report to do the following: if the next set of records add up to 2510 hours, I want page 2 to show the next set of records, that come close to the 2500, even if it is short, and carryover the remainder onto the next page. The code I am working with: If Me.Estimated Hour Required > 2500 Then Me.Section(acDetail).ForceNewPage = 2 Else Me.Section(acDetail).ForceNewPage = 0 Thanks, Spud > Hi Spud, > [quoted text clipped - 49 lines] > >>> that one item, and start a new page to begin calculating the next sum > >>> of 2500. Hi Spud, "record source for this field..." RecordSource is a property of a form or a report, not an individual control. "It is being entered..." what is being entered? "entered into a table" you should use forms and form/subforms for data entry ... okay, enough for terminology ... WHY do you want 2500 of expenses on each page? What is this number a 'threshold' for? Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * > Hi Crystal, > [quoted text clipped - 74 lines] >>>>> that one item, and start a new page to begin calculating the next sum >>>>> of 2500. Hi Crystal, Sorry it took so long to respond. I was gone for the holiday. Okay.... We use a form in access to enter a job. Each job has a name, description, dept, etc. The user enters each job and the number of hours it will take them to complete the job. This field is called: "Estimated hours required." Each month we only have 2,500 hours that is alloted to these jobs. If: Job 1 takes 1,000 hours Job 2 takes 1,000 hours Job 3 takes 1,000 hours I want only Jobs 1 and 2 to show up on this months report and Job 3 to be the first one to show up on the next page (which will be next months report) because it would take 3,000 hours to complete all three jobs and we only have 2,500 hours allotted. > Hi Spud, > [quoted text clipped - 101 lines] > >>>>> that one item, and start a new page to begin calculating the next sum > >>>>> of 2500. Since that is the way you are allocating, you could use a field in the table where you collect the estimated hours. This will be a calculated field (not a good idea but in this case, it will make things a lot easier). Before reports are run, you will run a program that allocates the hours. I would suggest you use a date field even though you may only be interested in month and year, the allocation day could be anything in that month and year but for convenience, just use 1. If you provide more information about your data structure, we can help you with the coding. Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * > Hi Crystal, > [quoted text clipped - 122 lines] >>>>>>> that one item, and start a new page to begin calculating the next sum >>>>>>> of 2500. Hi again... Thanks for all of your help... I was hoping that I could avoid adding a calculated field. I was looking for something that could be added to the report level. Possibly something that could be added to the detail or report footer of the report page that would restrict each page to the view that I am looking for. I'll try adding a calculation and let you know. Thanks, > Since that is the way you are allocating, you could use a field in the > table where you collect the estimated hours. This will be a calculated [quoted text clipped - 143 lines] > >>>>>>> that one item, and start a new page to begin calculating the next sum > >>>>>>> of 2500. Hi Spud don't blame you for not wanting to add the calculated field. You can, of course, put code on the Format event of the detail section and add things as they come through but I think it would be best to group by something... besides, this piece of information seems like it is important to save. It is not like adding a field to calculate profit from cost, you are creating a piece of information that you need to keep track of... Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * > Hi again... > [quoted text clipped - 155 lines] >>>>>>>>> that one item, and start a new page to begin calculating the next sum >>>>>>>>> of 2500. Hi Crystal: I put a code "Cancel = Me.txtCount > 2500" on the format event of the detail section of the report. This does exactly what I want the report to do for the first page. However, I need something similar that will limit each page to only 2500 hours. Any ideas? Thanks again... spud! > Hi Spud > [quoted text clipped - 175 lines] > >>>>>>>>> that one item, and start a new page to begin calculating the next sum > >>>>>>>>> of 2500. Hi Spud, 1. define a global variable at the top of the code behind the report '~~~~~~~~~~~~~~~~~~~~ dim mTotal as Long '~~~~~~~~~~~~~~~~~~~~ 2. initialize the variable in the Format event of the Report Header '~~~~~~~~~~~~~~~~~~~~ mTotal = 0 '~~~~~~~~~~~~~~~~~~~~ 3. increment and test the variable in the Format event of the Detail section '~~~~~~~~~~~~~~~~~~~~ mTotal = mTotal + me.HoursControlname if mTotal >= 2500 then mTotal = me.HoursControlname 'insert code to go to next page end if Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * > Hi Crystal: > [quoted text clipped - 185 lines] >>>>>>>>>>> that one item, and start a new page to begin calculating the next sum >>>>>>>>>>> of 2500. I understand # 2 and #3 but I need help with # 1. when you say "behind the report". Thanks, > Hi Spud, > [quoted text clipped - 219 lines] > >>>>>>>>>>> that one item, and start a new page to begin calculating the next sum > >>>>>>>>>>> of 2500. Hi Spud, When you are in the design view of the report, from the menu choose View, Code... there you will see the code you have written (or the Wizard has generated) for the report. This code is stored "behind" the report and is copied along with the report. If you dimension a variable BEFORE any procedures on a module sheet, it is global to the module ... this means that all Subs and Functions on the sheet can read it and change it. to help you understand Access a bit better, send me an email and request my 30-page Word document on Access Basics (for Programming) -- it doesn't cover VBA, but prepares you for it because it covers essentials in Access. Be sure to put "Access Basics" in the subject line so that I see your message... Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * > I understand # 2 and #3 but I need help with # 1. when you say "behind > the report". [quoted text clipped - 224 lines] >>>>>>>>>>>>> that one item, and start a new page to begin calculating the next sum >>>>>>>>>>>>> of 2500. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.