A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Complicated display in group footer



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2004, 06:56 PM
Jennifer
external usenet poster
 
Posts: n/a
Default Complicated display in group footer

Ok this is kind of complicated but here goes:
I have a field called Data that contains numbers. In the group footer I wand a box that colors itself based on the number in the "Data" field from the detail section like so:
if Data then Box quadrant Colored
1 all of box
2 top right hand quadrant
3 top left hand quadrant

It goes on like this and the quadrants are divided as well. There is never an overlap i.e. you would never have both a 1 and a 2 in the same group, there may be multiple numbers in the detail and I want all numbers represented in the same box.

  #2  
Old May 28th, 2004, 08:21 PM
Steve Schapel
external usenet poster
 
Posts: n/a
Default Complicated display in group footer

Jennifer,

There are 2 approaches that I can think of to this. Both of them
involve thinking of 4 separate boxes (arranged to look like quadrants of
a larger box), rather than trying to format quadrants of a rectangle.

1. Spell it out longhand in code on the Format event of the group
footer section, something like...
Select Case Me.Data
Case 1
Me.FirstQuadrantBox.BackColor = 255
Me.SecondQuadrantBox.BackColor = 255
... etc
Case 2
Me.FirstQuadrantBox.BackColor = 16777215
Me.SecondQuadrantBox.BackColor = 255
... etc
... etc
End Select

2. Make a table with these fields:
DataValue
FirstQuadrant
SecondQuadrant
ThirdQuadrant
FourthQuadrant
.... the quadrant fields being Yes/No data type, which you check them if
that quadrant should be coloured for each possible Data value. Then,
you can add this table to the query that the report is based on, joined
to the existing table on the Data field. Then, your "box" on the report
is comprised of 4 textboxes, which are bound to the xQuadrant fields
from the query, and use Conditional Formatting to manipulate their colour.

--
Steve Schapel, Microsoft Access MVP


Jennifer wrote:
Ok this is kind of complicated but here goes: I have a field called
Data that contains numbers. In the group footer I wand a box that
colors itself based on the number in the "Data" field from the detail
section like so: if Data then Box quadrant Colored 1
all of box 2 top right hand quadrant 3
top left hand quadrant

It goes on like this and the quadrants are divided as well. There is
never an overlap i.e. you would never have both a 1 and a 2 in the
same group, there may be multiple numbers in the detail and I want
all numbers represented in the same box.

  #3  
Old May 28th, 2004, 09:16 PM
Jennifer
external usenet poster
 
Posts: n/a
Default Complicated display in group footer

The first one worked great now it is only showing the last value from the detail any thoughts?
  #4  
Old May 28th, 2004, 10:10 PM
Steve Schapel
external usenet poster
 
Posts: n/a
Default Complicated display in group footer

Jennifer,

My assumption was that all records in the Group have the same value for
Data. Otherwise, it doesn't make sense to try to represent the Data
value in the box colouring... does it? I mean, if there are multiple
values for the Data field within the group, which one is it supposed to
use in the colouring of the box in the group footer? Maybe you could
give some more details and some examples if there is something I am not
understanding here.

--
Steve Schapel, Microsoft Access MVP

Jennifer wrote:
The first one worked great now it is only showing the last value from the detail any thoughts?

  #5  
Old June 1st, 2004, 04:16 PM
Jennifer
external usenet poster
 
Posts: n/a
Default Complicated display in group footer

OK here goes if 1 is all of the box you would not have any other number but it is possible to have a 2(top right) and a 4(bottom left) that need to be shaded in within the same group.
  #6  
Old June 1st, 2004, 08:28 PM
Steve Schapel
external usenet poster
 
Posts: n/a
Default Complicated display in group footer

Jennifer,

Ok, well you could put some unbound textboxes in the group footer
section, one for each value you need to test for the existence of within
the group. Their Control Sources can be set to something like...
=Sum([Data]=1)
=Sum([Data]=2)
etc.

Let's call these textboxes names like Test1, Test2, etc.

Then, you can write your Format event code something like this...

If Me.Test1 0 Then ' (means there is at least one 1 in the data)
Me.FirstQuadrantBox.BackColor = 255
Me.SecondQuadrantBox.BackColor = 255
... etc
End If
If Me.Test2 0 Then
Me.FirstQuadrantBox.BackColor = 16777215
Me.SecondQuadrantBox.BackColor = 255
... etc
End If
... etc

--
Steve Schapel, Microsoft Access MVP

Jennifer wrote:
OK here goes if 1 is all of the box you would not have any other
number but it is possible to have a 2(top right) and a 4(bottom left)
that need to be shaded in within the same group.

  #7  
Old June 1st, 2004, 09:06 PM
Jennifer
external usenet poster
 
Posts: n/a
Default Complicated display in group footer

Thank you!!!!!!!1
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:11 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.