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!
strive4peace wrote:
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
*
Spud wrote:
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,
strive4peace wrote:
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
*
Spud wrote:
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.
strive4peace wrote:
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
*
Spud wrote:
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
strive4peace wrote:
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
*
Spud wrote:
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
strive4peace wrote:
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
*
Spud wrote:
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.
