Report GroupBy Expression

In a report Grouping I'm trying to group by an expression.

Here is what the report looks like:

Screen Shot
I want the report to group by the Import Variance text and can't figure out how to do it.
SteveL13Asked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
Steve,

 Two problems:

1. Dim's - this:

Dim bolPreviousImport, bolNewImport As String

Should be this:

Dim bolPreviousImport As Boolean
Dim bolNewImport As Boolean

or

Dim bolPreviousImport As Boolean, bolNewImport As Boolean

  This is a common mistake.  That first variable ends up as a variant because you didn't specify a type.  That still would have worked, but the second should not be a string.

Second problem:   Take the code you have in the Detail's OnPrint event and move it to the OnFormat event.

 In two pass printing, there is no printing on the first pass.  So the code to set the flags never gets fired and everything ends up as "no variance"  (False, False for the two flags).

You get the footer though on the second pass because the print event is firing for the detail and your executing the code on for that both passes.

  But for the header, we only pickup the value on the first (non-print)  pass.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<I want the report to group by the Import Variance text and can't figure out how to do it. >>

 Your not going to be able to because your generating the text in the report on the fly.

 In order to do a Group by on that, it would need to be in the forms underlying data, or based on that (ie.  calculating years employed off a hire date and grouping by number of years).

 So either you:

1. Build it into the query if you can.

2. Build a temp table, adding a field to reflect the variance, then build the report off that.

Jim.
0
 
HainKurtSr. System AnalystCommented:
post the current sql for above screenshot...

post what you need...

so, we can say something...
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
As an FYI for others, Steve asked a question before this about generating text in the footer based on records in the detail section for the employee.

The technique I showed him was to do it on the fly in the report via VBA code.  

Jim.
0
 
SteveL13Author Commented:
Yes,  On the fly which is working great.  But somehow I need to make a text field in a new group replicate the text in the footer section.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Can a link to the previous Q be posted here?

Perhaps the question should have been "How do I add is data to the underlying data"...?

Perhaps I am not understanding something, but the report already looks like it is grouped by Import Variance...?
0
 
SteveL13Author Commented:
Link:

http://www.experts-exchange.com/Database/MS_Access/Q_28577135.html#a40489186

The report is grouped but instead of the way it's currently grouped I want the grouping description to appear in the group header, not the group footer.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Steve,

 Do you want the grouping the same, but just move the description to the header, or do you want to really group on the variance text (listing all of one type first, then the next, followed by the last)?

Jim.
0
 
SteveL13Author Commented:
I want the grouping to be the same.  Just move the description to the header.
0
 
Jeffrey CoachmanMIS LiasonCommented:
I am sure Jim will get you sorted,...
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<I want the grouping to be the same.  Just move the description to the header. >>

 All right, a little more code then.   What were going to do is put the report in "two pass" mode by referring to the pages property.  So first thing, add a hidden control in the page footer with it set to =[Page] & " of " & [Pages]

What the report engine will now do is run through the report once, so it can figure out the total pages.  Then on the second pass, it will actually print the report.

 What we'll do on that first pass is capture the text that your printing in the footer.   Then on the second pass, we'll use what we stored so we can place it in the group header.

So in the group footer On Format event, add the following code after you determine the text:

 If FormatCount = 1 then
   If Me.Pages = 0 Then
      ReDim Preserve GrpArrayName(lngGroupCount + 1)
      ReDim Preserve GrpArrayText(lngGroupCount + 1)
      GrpArrayName(lngGroupCount ) = Me.<control with group key>
      GrpArrayText(lngGroupCount ) = Me.<control with text>

     lngGroupCount = lngGroupCount + 1
   End If
 End If

Now in the Group Header OnFormat:

  Dim lngI as long

  If f Me.Pages > 0 then
  For lngI = 1 to lngGroupCount
         If GrpArrayName(lngI-1) =  Me!<control with group key> Then
         Me!<control with text> =    GrpArrayText(lngI-1)
         lngI = lngGroupCount
  Next lngI
 End If

in the report modules header:

Dim GrpArrayName(), GrpArrayText()
Dim lngGroupCount As Long

Now realize that anything shown with <....> above, you need to replace that with whatever your actually using.  For example, if EmpID was what your grouping on then:

Me!<control with group key>  

would be

Me![EmpID]

What you want to do is store the group key and the text on the first pass, then on the second, lookup what was stored and place it in a control in the header.

This is also a technique you would use to build a table of contents or an index.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
and by the way, leave the footer as is for the moment so you can check your work.  When you have it working, then you can hide the footer if there is nothing else there.

jim.
0
 
SteveL13Author Commented:
Jim,

Sorry for the delay.  I guess it must be holiday time!

Anyway,

the only Issue I am having with the code you provided is in:

    Dim lngI As Long
    If Me.Pages > 0 Then
    For lngI = 1 To lngGroupCount
          If GrpArrayName(lngI - 1) = Me!ChronoID Then
          Me!txtVarianceMessage = GrpArrayText(lngI - 1)
          lngI = lngGroupCount
    Next lngI
    End If

Open in new window


I'm getting an error:  Next without for on the line:

Next lngI

When I try to compile.
0
 
SteveL13Author Commented:
More info:

I hadn't put the hidden control on the report footer. Now I have done that.  It is a text field named "txtHidden" with this as the control source:  =[Page] & " of " & [Pages]

The code in the group header onformat event is:

    bolPreviousImport = False
    bolNewImport = False
    

    Dim lngI As Long
    If Me.Pages > 0 Then
    For lngI = 1 To lngGroupCount
          If GrpArrayName(lngI - 1) = Me!ChronoID Then
          Me!txtVarianceMessage = GrpArrayText(lngI - 1)
          lngI = lngGroupCount
    Next lngI
    End If

Open in new window


And the code in the group footer onformat even is:

   Me.txtVarianceMessage = "no variance"

   If bolPreviousImport = True And bolNewImport = True Then Me.txtVarianceMessage = "Medication Change"
   If bolPreviousImport = False And bolNewImport = True Then Me.txtVarianceMessage = "New Patient"
   If bolPreviousImport = True And bolNewImport = False Then Me.txtVarianceMessage = "Removed Patient"

   If FormatCount = 1 Then
    If Me.Pages = 0 Then
    ReDim Preserve GrpArrayName(lngGroupCount + 1)
    ReDim Preserve GrpArrayText(lngGroupCount + 1)
    GrpArrayName(lngGroupCount) = Me!ChronoID
    GrpArrayText(lngGroupCount) = Me.txtVarianceMessage

    ngGroupCount = lngGroupCount + 1
    End If
    
  End If

Open in new window


I don't know what I'm doing wrong.  Sorry.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Steve,

<<I don't know what I'm doing wrong.  Sorry. >>

 No problem.   First, get it to compile. At the top of your report module, make sure you have

Option Explicit


 now try to compile.   You need to be error free before you can continue.  Something is mistyped and the option explicit will help you find it.

 Also make sure you did:

 Dim lngGroupCount as long

 at the top of the module.

Jim.

PS. Yes, it is the holidays<g>, sorry for the delay.
0
 
SteveL13Author Commented:
Is now compiling.

But in the group header on format I get "Next without for" in:

    bolPreviousImport = False
    bolNewImport = False
    

    Dim lngI As Long
    If Me.Pages > 0 Then
    For lngI = 1 To lngGroupCount
          If GrpArrayName(lngI - 1) = Me!ChronoID Then
          Me!txtVarianceMessage = GrpArrayText(lngI - 1)
          lngI = lngGroupCount
    Next lngI
    End If

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
There's an End If missing:


    bolPreviousImport = False
    bolNewImport = False
   

    Dim lngI As Long

    If Me.Pages > 0 Then
      For lngI = 1 To lngGroupCount
          If GrpArrayName(lngI - 1) = Me!ChronoID Then
             Me!txtVarianceMessage = GrpArrayText(lngI - 1)
             lngI = lngGroupCount
          End If
      Next lngI
    End If
0
 
SteveL13Author Commented:
That fixed that.  But now all of the header read "no variance".  I am so sorry I just can't get this right.

Here is header on format:

    bolPreviousImport = False
    bolNewImport = False
     

Dim lngI As Long

    If Me.Pages > 0 Then
        For lngI = 1 To lngGroupCount
            If GrpArrayName(lngI - 1) = Me!ChronoID Then
             Me!txtVarianceMessage = GrpArrayText(lngI - 1)
             Me!txtHeaderVarianceDescription = GrpArrayText(lngI - 1)
             lngI = lngGroupCount
            End If
        Next lngI
    End If

Open in new window


And here is footer onformat:

   Me.txtVarianceMessage = "no variance"

   If bolPreviousImport = True And bolNewImport = True Then Me.txtVarianceMessage = "Medication Change"
   If bolPreviousImport = False And bolNewImport = True Then Me.txtVarianceMessage = "New Patient"
   If bolPreviousImport = True And bolNewImport = False Then Me.txtVarianceMessage = "Removed Patient"

   If FormatCount = 1 Then
   
    If Me.Pages = 0 Then
    ReDim Preserve GrpArrayName(lngGroupCount + 1)
    ReDim Preserve GrpArrayText(lngGroupCount + 1)
    GrpArrayName(lngGroupCount) = Me!ChronoID
    GrpArrayText(lngGroupCount) = Me.txtVarianceMessage

    lngGroupCount = lngGroupCount + 1
    End If
    
  End If

Open in new window


And I do still have this text box in the report footer: (control source)

=[Page] & " of " & [Pages]
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<That fixed that.  But now all of the header read "no variance".  I am so sorry I just can't get this right.>>

  No worries.  Live and learn as they say.

 So first, in the header code, delete this line:

           Me!txtVarianceMessage = GrpArrayText(lngI - 1)

as it's not needed.  You have another control for the message in the header.

 Now to trouble shoot.    First, we have to make sure we're picking up the value, then getting the right value for the header.

 In the Footer's code, place a break point on:

If FormatCount = 1 Then

 (click on the line, and then press F9)

 In the header's code, place a break point on this line:

 If Me.Pages > 0 Then

Now execute the report.

You should stop in the header's on format event.   You can hover over variables and references to see the value.  On the first pass, Me.Pages should be 0.    Now press F8 (single step) and you should find that the next line to execute is the END IF and all the code for setting the control gets skipped.

Now press F5 and execution will continue.

Should now hit the stop in the footer.   If you hit F8, you should find that your on the first REDIM statement.  Hit F8 a couple of more times.  Each time you press F8, a line of code will execute.  So get down to the line:

lngGroupCount = lngGroupCount + 1

Then hover over:

GrpArrayName(lngGroupCount)

and

GrpArrayText(lngGroupCount)

 The "key" for the group should be in the first and the correct message should be in the second.   If it is, then the values are getting stored properly.

 Now hit F5 and continue on.   Again, you can verify the group is getting stored correctly.

 At some point, you will get to pass 2....you may want to add a filter the report to limit it a bit so you only get a couple of groups while your testing this.

 Now on the second pass, the code in the header should come into play.   When you hit:

   If Me.Pages > 0 Then

 and Press F8, you should drop into the For /Next loop, which finds the "key" for the group, and then places the text into your control:

Me!txtHeaderVarianceDescription = GrpArrayText(lngI - 1)

 to display the message.

Jim.
0
 
SteveL13Author Commented:
Jim,

Ok. I tried.  Still can't get it.  So I have stripped out all confidential info and objects and attached a file.  If you run the report you'll see what I'm having trouble with.  Can you let me know what I've done wrong or even change it and return the file?

--Steve
Report-Issue.zip
0
 
SteveL13Author Commented:
PERFECT!!!!  Now I don't know which suggestion to award to you.  I guess the last one, correct?
0
 
SteveL13Author Commented:
Perfect!  Thank you.
0
All Courses

From novice to tech pro — start learning today.