How can I make this logic more variable dependent

mytfein
mytfein used Ask the Experts™
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.

Please advise, tx, sandra
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Which parts ?

mx

Author

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
could possibly earn additional points.

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)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

Author

Commented:
Tx mx, s

Author

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
 
If Me.txtFinalGrade = "HP" Then
   Me.lnHP.Visible = True
   Me.ArrowHP.Visible = True
Else
   Me.lnHP.Visible = False
   Me.ArrowHP.Visible = False
End If
 
If Me.txtFinalGrade = "H" Then
   Me.lnH.Visible = True
   Me.ArrowH.Visible = True
Else
   Me.lnH.Visible = False
   Me.ArrowH.Visible = False
End If
 
If Me.txtFinalGrade = "P" Then
   Me.lnP.Visible = True
   Me.ArrowP.Visible = True
Else
   Me.lnP.Visible = False
   Me.ArrowP.Visible = False
End If
 
 
End Sub

Open in new window

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)
and for your questions why:
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
about finding the right height:
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
 

Author

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

thx for more info...   i really appreciate your help...

let's start with vba  window:
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)

d) I'm confused about this line:
   <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

Author

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
                               and we are dividing, instead:
                                       MaxHeight / 100

tx, s      

Author

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

Please advise if you can on prev. comments, as well, tx, s

Adjusted the formula:
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

Open in new window

Author

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)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

Author

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

Author

Commented:
Hi Erez,

Thx again sooo much for your help,
in the code window is my adaptation of your code
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

Open in new window

VertSelfGraph3.GIF
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
good job !!

mx

Author

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

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

Author

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

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial