# How can I make this logic more variable dependent

on
rctHPbar.Top = rctHPbar.Top - ((Me.txtHPcount / 100) * (1440 * lngBarMaxInches)-rctHPbar.Height )

rctHPbar.Height = (Me.txtHPcount / 100) * (1440 * lngBarMaxInches)

Hi EE,
This is tentative logic to resize a rectangle from help that I got in the related post below.
I'd like to break up this logic into variable pieces so that i can test and understand.

Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Architect / Application Developer
Top Expert 2007

Commented:
Which parts ?

mx

Commented:
Hi mx,
thx for writing...
You will see above theat yesterday's post that you participatedit has some of the logic already
contributed by Erez.

I did not want to leave yest. post open for much longer bec. you all offered info.
I wanted to close it as resolved, and explained to Erez that will open a related post, so that he

and so, I hope he won't be disappointed that we have already begun this discussion without him....
ok, this is the logic Erez provided:

now for the formula:
check for the maximum size you want for the bar, and create
const MaxHeight As Long=(the maximum value from above comes here)

and the lowest point your bar reaches (the base of the bar), and create
const StartTop As Long=(the value from above comes here)

now comes
BarLength=MaxLength/100*Me.txtHPcount
BarTop=startTop - BarLength

and now it's time for
rctHPbar.Top = BarTop
rctHPbar.Height = BarLength

I had the following quests for Erez:
a) Does long carry a decimal
or should I be using double

so Erez answere to use cLng - not sure why? is it bec. controls are variant?

b) Erez is offering the constansts of startTop and MaxHeight.
Does Erez want me to test the calc height to maxheight to
avoid the errors I'm getting about control being too large?

c) If you look at top property of control, it says inches
when you refer to top property invba, is it inches or pixels or twips - i'm getting confused
not sure what top is being set to - and if it's not inches, do we have to convert it back to inches.

tx, s
Database Architect / Application Developer
Top Expert 2007

Commented:
a) Does long carry a decimal

No. 'Long' is a (long) integer ... no decimal.  Use Single if you need decimal pts.  (Or Double or Decimal).

"is it bec. controls are variant?"
Controls are Objects.  What's *in* a Control (value ) could be a variant (or String, Date, etc).

Have to run to meeting ... Erez should be along.

mx

Commented:
Tx mx, s

Commented:
Hi, this is logic so far:
My series is 3 bars: HP, P, H
So the logic repeats itself 3 times, and posted into code window

Below am posting one interation:

Please note this line that I am unsure of:
dblDefaultTop = 0.8021 / 1440
.8021 is inches of top property,
so I guess I have to divide by 1440 ?

=========
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim lngMaxStudents  As Long
Dim lngBarMaxInches  As Long

Dim dblBarMaxInches As Double
Dim dblBarLength    As Double
Dim dblDefaultTop   As Double
Dim dblRevisedTop  As Double

lngBarMaxInches = 3
lngMaxStudents = 250

dblDefaultTop = 0.8021 / 1440

rctHPbar.Height = 0
rctHbar.Height = 0
rctPbar.Height = 0

Debug.Print Me.txtCourse
Debug.Print Me.txtHPcount

Debug.Print Me.rctHPbar.Top

If Not IsNull(Me.txtHPcount) Then

dblBarLength = (Me.txtHPcount / lngMaxStudents)
Debug.Print dblBarLength
dblRevisedTop = dblDefaultTop - dblBarLength

rctHPbar.Top = dblRevisedTop
rctHPbar.Height = dblBarLength * (1440 * lngBarMaxInches)

rctHPbar.Visible = True
Else
rctHPbar.Height = 0
rctHPbar.Visible = False
End If

``````Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim lngMaxStudents  As Long
Dim lngBarMaxInches  As Long

Dim dblBarMaxInches As Double
Dim dblBarLength    As Double
Dim dblDefaultTop   As Double
Dim dblRevisedTop  As Double

lngBarMaxInches = 3
lngMaxStudents = 250

dblDefaultTop = 0.8021 / 1440

rctHPbar.Height = 0
rctHbar.Height = 0
rctPbar.Height = 0

Debug.Print Me.txtCourse
Debug.Print Me.txtHPcount

Debug.Print Me.rctHPbar.Top

If Not IsNull(Me.txtHPcount) Then

dblBarLength = (Me.txtHPcount / lngMaxStudents)
Debug.Print dblBarLength
dblRevisedTop = dblDefaultTop - dblBarLength

rctHPbar.Top = dblRevisedTop
rctHPbar.Height = dblBarLength * (1440 * lngBarMaxInches)

rctHPbar.Visible = True
Else
rctHPbar.Height = 0
rctHPbar.Visible = False
End If

If Not IsNull(Me.txtHCount) Then
dblBarLength = (Me.txtHCount / lngMaxStudents)
Debug.Print dblBarLength
dblRevisedTop = dblDefaultTop - dblBarLength

rctHbar.Top = dblRevisedTop
rctHbar.Height = dblBarLength * (1440 * lngBarMaxInches)

rctHbar.Visible = True
Else
rctHbar.Height = 0
rctHbar.Visible = False
End If

If Not IsNull(Me.txtPcount) Then
dblBarLength = (Me.txtPcount / lngMaxStudents)
Debug.Print dblBarLength
dblRevisedTop = dblDefaultTop - dblBarLength

rctPbar.Top = dblRevisedTop
rctPbar.Height = dblBarLength * (1440 * lngBarMaxInches)

rctPbar.Visible = True
Else
rctPbar.Height = 0
rctPbar.Visible = False
End If

Me.lnHP.Visible = True
Me.ArrowHP.Visible = True
Else
Me.lnHP.Visible = False
Me.ArrowHP.Visible = False
End If

Me.lnH.Visible = True
Me.ArrowH.Visible = True
Else
Me.lnH.Visible = False
Me.ArrowH.Visible = False
End If

Me.lnP.Visible = True
Me.ArrowP.Visible = True
Else
Me.lnP.Visible = False
Me.ArrowP.Visible = False
End If

End Sub
``````

Commented:
hello again
got my points already - so no points for me from now on. it's for the fun of it
i'm sorry but i forgot something:
BarLength=MaxLength/100*Me.txtHPcount
Should be
BarLength=cLng(MaxLength/100*Me.txtHPcount)
size and position of a control on a form or report are always whole numbers (the pixels or twips cannot be split into half), so the cLng function rounds the formula x/y to the closest long (long integer) value, which should be good enough

" so Erez answere to use cLng - not sure why? is it bec. controls are variant?"
it is not the control were setting, it's his height and top properties, which are of long type
in vba window, when editing the code for your report/form, find the rectangle in the properties window (usually on the lower left side of the screen), the value you see there is the value you need for the Const variables  in vba you use twips, and no conversion is needed
for b) and c):
assuming from the code you provided, i figured that your textbox txtHPcount is some kind of variable that might have values ranging from 0 to 100. if that is so, then setting barLength to  cLng(MaxLength/100*Me.txtHPcount) will produce a value that is the linear equivalent to the scale 0 to 100, only with values from 0 to Maximum Bar Length
in other words: if 0 in txtHPcount  should be a zero-high bar then how high the bar should be if txtHPcount =100? the answer to that should be saved in MaxLength
hope that is clearer now

Commented:
Hi Erez,
sorry for delay in writing... went thru your response very slowly

a) usually i have properties window hidden,     below is a screen shot  for:  rctHPbar:
height = 4605
top = 1155

b) Now I should modify this code to be?
==============================
const MaxHeight As Long=4320
const StartTop    As Long=1155

c) I made the bar in the report 3 inches on purpose to understand the vba window.
great I see that it says 4320 for heiight ( 3 *1440)

<size and position of a control on a form or report are always whole numbers (the pixels or twips cannot be split into half), so the cLng function rounds the formula x/y to the closest long (long integer) value, which should be good enough>

In design mode, I have seen height with decimal (inches)
Do you mean that the inches gets converted to a rounded twips number in the vba window?

e) now the hard part (for me)
graph shows graphically how the students scored
bar - HP - high honors
bar - H HONORS
bar - P - PASS

let's say 17 students got HP,
so I thought
17/100 = .17   *  (1140 * 3 ) 3 inches being max
just show 17% of the 3 inches bar

does that make sense?

( btw, i think i have to modify the 100 to 250 bec. we have more than 100  students, more like 250.)

and so,  i  am not understanding:
BarLength=cLng(MaxLength/100*Me.txtHPcount)

==>        maybe should be based on your orig writing to use const: maxHeight

BarLength=cLng(MAXheight  /   100 * Me.txtHPcount)

==>      maybe need more parenthesis like thsi

BarLength =  cLng  (MAXheight  /   100)   *    Me.txtHPcount)

==>       If i plug into values into this formula, am not understanding:
BarLength   =  cLng  (4320 / 100)  * 17 )
432 * 17   = 7344
7344 is larger than 4320.
i just want to show 17 % of 3 inches.

Pls advise on this formula.... maybe i'm missing something... tx, s

vbaPropertiesWindow.GIF

Commented:
Hi Erez,

oops, i miscalculated - just used a calculator:

BarLength   =  cLng  (4320 / 100)  * 17 )
43.2 * 17 =  734.4

ok, but i still do not understand why we are not dividing 17/100
MaxHeight / 100

tx, s

Commented:
Hi Erez,I believe I got it:
a) calculate ** max top **:
StartTop + maxBarHeight
b) subtract the calc height from the **max top**
to get the RevisedTop

lngRevisedTop = lngStartTop + lngMaxHeight
lngRevisedTop = lngRevisedTop - lngBarLength

If Not IsNull(Me.txtPcount) Then
' calcs in twips must be round numbers, clng does rounding
' lngMaxHeight already in twips (got from vba/view/properties screen
'                         of rectangle

lngBarLength = CLng(lngMaxHeight / 100 * Me.txtPcount)

Debug.Print lngBarLength
lngRevisedTop = lngStartTop + lngMaxHeight
lngRevisedTop = lngRevisedTop - lngBarLength

rctPbar.Top = lngRevisedTop
rctPbar.Height = lngBarLength

rctPbar.Visible = True
Else
rctPbar.Height = 0
rctPbar.Visible = False
End If

Commented:
hello again
now that i know what the bar actually represents, and since it's not a percentage of anything, just the number of students scoring HP (or H or P for the other two bars), i'll suggest that:
forget about the 100 (it's because i thought we're calculating percentage)
if you have approximately 250 students, and you want your bar to withstand the unlikely event of having them all scoring HP, then think of the Full length bar being built from 250 pieces (one for each student). ok so far?
if a full bar is 4320, then 1 student "worths" 4320/250=17.28
since you probably expect the number of students to change, calculate that value at the begining of the procedure
OneStudent=4320/Number Of Students
this variable should be single or double (cause for now you dont want to lose the decimal value)
look at the code i attached

``````Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim OneStudent As Double
Dim barHeight As Long, barTop As Long

Const FullBarTop As Long = 1395

OneStudent = 4320 / 250

rctHPbar.Height = 0
rctHbar.Height = 0
rctPbar.Height = 0

If Not IsNull(Me.txtHPcount) Then
barHeight = CLng(OneStudent * Me.txtHPcount)
barTop = FullBarTop + 4320 - barHeight
rctHPbar.Height = barHeight
rctHPbar.Top = barTop
rctHPbar.Visible = True
Else
rctHPbar.Height = 0
rctHPbar.Visible = False
End If

If Not IsNull(Me.txtHcount) Then
barHeight = CLng(OneStudent * Me.txtHcount)
barTop = FullBarTop + 4320 - barHeight
rctHbar.Height = barHeight
rctHbar.Top = barTop
rctHbar.Visible = True
Else
rctHbar.Height = 0
rctHbar.Visible = False
End If

If Not IsNull(Me.txtPcount) Then
barHeight = CLng(OneStudent * Me.txtPcount)
barTop = FullBarTop + 4320 - barHeight
rctPbar.Height = barHeight
rctPbar.Top = barTop
rctPbar.Visible = True
Else
rctPbar.Height = 0
rctPbar.Visible = False
End If

'the rest of your code here...

End Sub
``````

Commented:
Hi Erez,

no error msgs (so far, I'm running with all courses)
as you can see in the screen shot below....

I'd like to stretch the arrow, though and am stuck on that formula
the arrow is a line, and the pointer is from start/pgms/accessories/systems/character set/ wingdings3

any idea, tx again sooo much, s  (leaving for day...)
HorizSelfGraph.GIF
Database Architect / Application Developer
Top Expert 2007

Commented:
"got my points already - so no points for me from now on."
s ... be SURE to give ErezMor ALL the pts on this Q :-)

mx

Commented:
Hi mx,
SURE thing, thx for your blessing....

Hi Erez,
Thx soooo much,
I have to go to a meeting in a few minutes, and just studied your code....
You analyzed the oneStudent concept and calc. beautifully...
I could not think of it (I was stuck on percentage of)

Will modify code and get back to you.... issues are just about resolved....

tx again, s

Commented:
Hi Erez,

Thx again sooo much for your help,
It works beautifully....

Below is a screen shot:

This quest. is officially closed. THANK YOU SOOOO  MUCH!!!!

i have 2 issues for which i may create new posts:
- landscaping this report
- converting this code into a generic procedure, so that the controls are not hardcoded in vba
(I have an issue when I try to landscape the report, so I may post a new quest....
I'd also like to make the 3 iterations of code more generic... feeding parms into a procedure etc,)
tx, s

``````Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim lngMaxStudents         As Long

Dim lngMaxHeightINCHES     As Long
Dim lngMaxHeightTWIPS      As Long
Dim lngRevisedBarHeight    As Long

Dim lngStartTop            As Long
Dim lngEndTop              As Long
Dim lngRevisedBarTop       As Long

Dim dblOneStudent          As Double

Const TWIPS As Long = 1440

' in twips - from vba properties screen
lngStartTop = 735

' convert bar from inches ( in design view properties) to TWIPS
lngMaxHeightINCHES = 3
lngMaxHeightTWIPS = lngMaxHeightINCHES * TWIPS

' get one unit on bar for student
lngMaxStudents = 250
dblOneStudent = lngMaxHeightTWIPS / lngMaxStudents

rctHPbar.Height = 0
rctHbar.Height = 0
rctPbar.Height = 0

Debug.Print Me.txtCourse

If Not IsNull(Me.txtHPcount) Then
' to have a bar grow VERTICALLY is not possible in Access in an easy way
' bec. adj. the height prop, makes the bar grow SOUTHWARD bec. Access tracks
' controls with top/left

' By re-calc the top, to a lower number, we can simulate a NORTHWARD growth
' of bar

' a) http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24587827.html#a24915631
' b) http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24584887.html

' c) calcs in twips must be round numbers, clng does rounding
' d) lngMaxHeight already in twips (got from vba/view/properties screen
'                         of rectangle
' e) MaxStudents

' Clng rounds result, twips can only be set in round numbers
lngRevisedBarHeight = CLng(dblOneStudent * Me.txtHPcount)
Debug.Print lngRevisedBarHeight
' Calc min, if bar would be soooo tiny like .1
lngEndTop = lngStartTop + lngMaxHeightTWIPS

' Recalc top: subtract actual bar size from Endtop,
'     to start building the bar SOUTHWARDS from a lower top,
'     to achieve a NORTHWARDS effect

lngRevisedBarTop = lngEndTop - lngRevisedBarHeight

Debug.Print dblOneStudent
Debug.Print Me.txtHPcount
Debug.Print lngRevisedBarHeight
Debug.Print lngStartTop; lngMaxHeightTWIPS
Debug.Print lngEndTop
Debug.Print lngRevisedBarTop

rctHPbar.Top = lngRevisedBarTop
rctHPbar.Height = lngRevisedBarHeight

rctHPbar.Visible = True
Else
rctHPbar.Height = 0
rctHPbar.Visible = False
End If

'---- 2 more iterations of code
``````
VertSelfGraph3.GIF
Database Architect / Application Developer
Top Expert 2007

Commented:
good job !!

mx

Commented:
mx, you're very kind... tx, s

Commented:
thanks for the points, again. and good luck

Commented:
Hi,
only if you have time....
just opened a related quest...

http://www.experts-exchange.com/Microsoft/Development/MS_Access/editQuestion.jsp?qid=24592586

Do more with