Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Report GroupBy Expression

Posted on 2014-12-09
22
Medium Priority
?
160 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 58
ID: 40489400
<<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 61

Expert Comment

by:HainKurt
ID: 40489404
post the current sql for above screenshot...

post what you need...

so, we can say something...
0
 
LVL 58
ID: 40489513
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:SteveL13
ID: 40489689
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
ID: 40489829
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
ID: 40489859
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 58
ID: 40489951
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
ID: 40489970
I want the grouping to be the same.  Just move the description to the header.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40489981
I am sure Jim will get you sorted,...
0
 
LVL 58
ID: 40490053
<<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 58
ID: 40490056
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
 

Author Comment

by:SteveL13
ID: 40500572
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
ID: 40500601
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 58
ID: 40501579
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
ID: 40501629
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 58
ID: 40502491
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
ID: 40502549
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 58
ID: 40502612
<<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
ID: 40502766
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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 40502994
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
ID: 40503015
PERFECT!!!!  Now I don't know which suggestion to award to you.  I guess the last one, correct?
0
 

Author Closing Comment

by:SteveL13
ID: 40503177
Perfect!  Thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

773 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