Solved

Report GroupBy Expression

Posted on 2014-12-09
22
147 Views
Last Modified: 2014-12-16
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.
0
Comment
Question by:SteveL13
  • 10
  • 9
  • 2
  • +1
22 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
post the current sql for above screenshot...

post what you need...

so, we can say something...
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
I want the grouping to be the same.  Just move the description to the header.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I am sure Jim will get you sorted,...
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:SteveL13
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
 

Author Comment

by:SteveL13
Comment Utility
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
PERFECT!!!!  Now I don't know which suggestion to award to you.  I guess the last one, correct?
0
 

Author Closing Comment

by:SteveL13
Comment Utility
Perfect!  Thank you.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now