Hi Duane,
The upperlevel group is EmployeeName then the next level group is CodeName and then we have the detail section.
So here is an example of a report:
Staff: John Lazy
CodeName: R-5678
Day Client Time Comment Total
1 Sill 3.00 Busy time 3.00
9 Sill 4.00 More time 4.00
Total for R-5678 7.00
CodeName G-5656
Day Client Time Comment Total
13 Lefty 3.00 Busy time 3.00
Total for G-5656 3.00
I have txtGroupCount in the EmployeeName header and it gives me the total number of records against each employee. So for John Lazy txtGroupCount gives me a count of 3 records.
I have txtCodeCount in the CodeName header and it give me a count of the number of codes used in that group. But here is where the exception comes in. txtCodeCount which is a running sum over group gives me a count of 2 codes but actually we are using one code twice so I am looking for 3 codes. The reason is that we are using txtCodeCount and txtGroupCount to determine whether the record is the last record in the group or not.
The problem occurs when the last record is part of this group and it does not spill over to the next page then I am using this to determine what caption I would like in the footer in a txtContinue variable. If group does not spill over to the next page then caption in txtContinue is blank otherwise it is "Continued on the next page"
If this is the last record and it does not spill over to the next page then the caption should be blank but since txtCodeCount and txtGroupCount are not equal we have txtUnimportant = "Not Last" and in this case the caption in txtContinue will be "Continued on the next page," which would be incorrect.
No problem really, someone already asked my why I do not leave this to the intelligence of the reader, but on some reports there is a whole lot of data, page after page, and it turns out to be a nice feature that is useful to many readers.
My apologies for being so long winded. I hope this clarifies the issue.
Thanks again for your time and patience.
Tom.
--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
>
> Tom,
>
> I'm confused. Do you have another grouping level between CodeHeader and the detail section?
>
> Perhaps you should provide some sample records and desired display in the report.
>
> Duane Hookom
> MS Access MVP
>
>
>
>
> To: MS_Access_Professionals@yahoogroups.com
> From: kaluuma@...
> Date: Thu, 22 Sep 2011 19:30:06 +0000
> Subject: [MS_AccessPros] Re: Last Record of a Group in a Report
>
>
>
>
>
>
> Thank-you Duane,
>
> That solved most of the exceptions I was finding in my reports and for this I am very thankful. I only add this exception in case you still have the energy to guide me to a solution. The exception is when the same CodeName is used more than once in a billing period against a particular client. ie. It is used on two different days say,
> for example:
>
> I have the CodeName in the CodeHeader and that is where I have the txtCodeCount.
>
> In the Deatil Section I have Day and all the other details associated with CodeName. Also I have a txtLineCount here that is a running sum over group.
>
> The IIf statement in txtUnImportant, which is in the deatail section, is as follows:
> IIf([txtCodeCount]=[txtGrpCount],"Is Last","Not Last")
>
> The output of txtCodeCount is:
> txtCodeCount = 1 for CodeName1. If I use CodeName1 on another day it appears under CodeName1 but is not counted twice so it still is txtCodeCount =1 and then that results in txtCodeCount being off by one and thus the IIf statement will give us ("Not Last")
>
> Any suggestions, again thanks for your help in clearing up most of my exceptions.
>
> Tom.
>
> --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> >
> >
> > No code required for this.
> >
> > Add a text box in the Group Header:
> > Name:txtGrpCount
> > Control Source: =Count(*)
> > Visible: No
> >
> > Add a text box in the detail section:
> > Name: txtRunSum
> > Control Source: =1
> > Running Sum: Over Group
> > Visible: No
> >
> > Add another text box in the detail section
> > Name: txtUnImportant
> > Control Source: =IIf([txtRunSum]=[txtGrpCount],"Is Last","Not Last")
> > Visible: of course
> >
> > Duane Hookom
> > MS Access MVP
> >
> >
> >
> >
> > To: MS_Access_Professionals@yahoogroups.com
> > From: kaluuma@
> > Date: Thu, 22 Sep 2011 16:45:29 +0000
> > Subject: [MS_AccessPros] Last Record of a Group in a Report
> >
> >
> >
> >
> >
> >
> > Hi,
> >
> > I have a report Grouped on CodeName and Sorted on CodeName with 'A on top'.
> > Is it possible to find the last record in a group on a report. I am thinking of a Yes/No this is the last record in this group. If so, can one then use an event such as 'on print' to set a variable.
> >
> > For example:
> >
> > Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)
> > If Me.txtNum = Me.txtCount And Me.txtGrCount = 1 Then
> > Me.txtbox = ""
> > Else
> > If (this is the last record in the group) Then .... I do not know what the IF argument would be for Last Record in a Group thus the parenthesis.
> > me.txtbox = "Last Record"
> > end if
> > Me.txtbox= "Not the last record"
> > End If
> > End Sub
> >
> > Thanks for any help.
> >
> > Tom.
> >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar