Bar graph showing percentage complete/overall progress (1 Viewer)

ginabenina

Registered User.
Local time
Today, 01:35
Joined
Mar 21, 2013
Messages
32
Hello,
Any help is greatly appreciated. I'm trying to show the progress of each project based on milestones with the activities ranging from 1 - 100. A single bar graph for each project.
I was trying to use the following set in the On Format of the detail section with no luck (http://www.tek-tips.com/viewthread.cfm?qid=1749100):

Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim int100Pct As Integer  ' width of 100 Percent
    int100Pct = 1440 * 6      ' 6 inches
    Me.txtPC.Width = Me.txtPC / 100 * int100Pct
End Sub

The control source of txtPC are Long Integers. Not sure what I am missing. :banghead:
 

isladogs

MVP / VIP
Local time
Today, 09:35
Joined
Jan 14, 2017
Messages
18,186
Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim int100Pct As Integer  ' width of 100 Percent
    int100Pct = 1440 * 6      ' 6 inches
    Me.txtPC.Width = Me.txtPC / 100 * int100Pct
End Sub

The control source of txtPC are Long Integers. Not sure what I am missing. :banghead:

A bracket! Either that or rearrange the formula!
Currently you are DIVIDING by int100Pct but you need to go forth and MULTIPLY

Code:
Me.txtPC.Width = (Me.txtPC / 100) * int100Pct
or

Code:
Me.txtPC.Width =  int100Pct*Me.txtPC / 100
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:35
Joined
May 7, 2009
Messages
19,169
What is the value of txtPc? Is it the percentage already?
 

ginabenina

Registered User.
Local time
Today, 01:35
Joined
Mar 21, 2013
Messages
32
Thank you for the reply.

Just tried adding brackets, rearranging, still not working.

The value is currently a Long Int, not formatted as anything. I did have it as a double, formatted as a percentage at one point to no avail.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:35
Joined
May 7, 2009
Messages
19,169
So you divide it with the Goal value or the overall total, whatever then you have percentage then multiply by int100pct variable.
 

Mark_

Longboard on the internet
Local time
Today, 02:35
Joined
Sep 12, 2017
Messages
2,111
Please take a look at the sample attached. It shows several ways of doing graphing like this in ACCESS.
 

Attachments

  • Custom Charting.accdb
    744 KB · Views: 142

ginabenina

Registered User.
Local time
Today, 01:35
Joined
Mar 21, 2013
Messages
32
The width of the textbox is not changing at all.
Code:
Dim int100Pct As Integer
    int100Pct = 1440 * 6
    Me.txtPC.Width = (Me.txtPC / 100) * int100Pct

Should the number be a Double opposed to a Long Int?

Taking a look now at the Custom Charting.
 

ginabenina

Registered User.
Local time
Today, 01:35
Joined
Mar 21, 2013
Messages
32
I am unable to run the report, Run-time error '1200': control or subform control too large for this location.
Compiler stops here,
Code:
 Me.C_Duration.Width = aiLeng 'and   setting the real width

Running Access 2016
 

Mark_

Longboard on the internet
Local time
Today, 02:35
Joined
Sep 12, 2017
Messages
2,111
Question,

What type of control is txtPC? Where are you getting the value for txtPC as you are using it in your calculation.

For myself, I would one control for data entry (or get it from a table/query/function) and a second for display.

If txtPC is a label then you would need to reference Me.txtPC.Caption to get the value it is displaying. If it is a text box that you are using for data entry, do you really want to change the size when a user enters data into it? If they put in a 0, that means they would need to leave the form and reopen it to be able to change it.
 

ginabenina

Registered User.
Local time
Today, 01:35
Joined
Mar 21, 2013
Messages
32
It is a textbox, the value is coming from a table as a Long Int, populating a report. The idea was to have the text box resize based on the percentage complete to mimic a bar graph. Should i be approaching this completely differently?
 

isladogs

MVP / VIP
Local time
Today, 09:35
Joined
Jan 14, 2017
Messages
18,186
I do this type of chart a lot in both forms & reports & its fairly simple to set up though you often need to tweak the scaling to get it to work

Attached are 3 screenshots from a report with several subreports with bar charts
- full report / report design & one of the subreports full size

This the full code to achieve the subreport shown with explanatory comments
Its more complex than you want but the idea is the same

Similar code is used in each subreport & in various forms

Code:
Dim ScalingFactor As Integer
Const CHARTWIDTH = 6  'multiplying factor to fit the space available
Const TwipsNumber As Integer = 567 (twips per cm)

Function GetScale()
'get size of largest value for chart
GetScale = Nz(DMax("CountOfPastoralRecordID", "qryPRecordsByFaculty", "PupilID = '" & Me.PupilID & "'"), 0)  
End Function

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

'Bar1 is the textbox used to create the bars in the chart
Me.Bar1.Width = CHARTWIDTH * TwipsNumber * Me.CountOfPastoralRecordID \ ScalingFactor

If Me.Bar1.Left + Me.Bar1.Width + TwipsNumber * 0.1 > (CHARTWIDTH + 0.5) * TwipsNumber Then
    'large value - show value in white font on top of bar
    Me.txtCount.Left = Me.Bar1.Left + Me.Bar1.Width - 1 * TwipsNumber
    Me.txtCount.ForeColor = vbWhite
Else 'small value - show value in black font to right of bar
    Me.txtCount.Left = Me.Bar1.Left + Me.Bar1.Width + TwipsNumber * 0.3
    Me.txtCount.ForeColor = vbBlack
End If

End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'add label to show maximum value fitting space available
'this is rounded up from max value used in chart
ScalingFactor = Round((GetScale + 5) / 10, 0) * 10
Me.LblScale.Caption = "(Full Width = " & ScalingFactor & ")"
End Sub


Hope that helps - feel free to ask questions if necessary
 

Attachments

  • ReportCharts.PNG
    ReportCharts.PNG
    51.8 KB · Views: 104
  • ReportDesign.PNG
    ReportDesign.PNG
    42.6 KB · Views: 115
  • SubReport.PNG
    SubReport.PNG
    8.1 KB · Views: 109

Mark_

Longboard on the internet
Local time
Today, 02:35
Joined
Sep 12, 2017
Messages
2,111
Sorry about the error.

I had been tinkering with it and gotten distracted. The report defaults to using the current year and I hadn't checked for 2018 when I put that together last year. Please see the attached updated.

For myself, on a report I would be using a label. That way I don't forget and try to do something silly with it.
 

Attachments

  • Custom Charting.zip
    88.5 KB · Views: 117

Mark_

Longboard on the internet
Local time
Today, 02:35
Joined
Sep 12, 2017
Messages
2,111
If Me.Bar1.Left + Me.Bar1.Width + TwipsNumber * 0.1 > (CHARTWIDTH + 0.5) * TwipsNumber Then
'large value - show value in white font on top of bar
Me.txtCount.Left = Me.Bar1.Left + Me.Bar1.Width - 1 * TwipsNumber
Me.txtCount.ForeColor = vbWhite
Else 'small value - show value in black font to right of bar
Me.txtCount.Left = Me.Bar1.Left + Me.Bar1.Width + TwipsNumber * 0.3
Me.txtCount.ForeColor = vbBlack
End If[/CODE]

Would it trouble you if I blatantly used this same logic in my demo? I've really been meaning to get back to it and this is a very nice touch!
 

isladogs

MVP / VIP
Local time
Today, 09:35
Joined
Jan 14, 2017
Messages
18,186
Would it trouble you if I blatantly used this same logic in my demo? I've really been meaning to get back to it and this is a very nice touch!

Feel free - if I put it on the forum, its there for others to use ....
The subreport screenshot shows the effect well

EDIT: I also posted lots of other screenshots of the same idea (used in forms & reports) in various posts in this thread
https://www.access-programmers.co.uk/forums/showthread.php?t=297309
 
Last edited:

ginabenina

Registered User.
Local time
Today, 01:35
Joined
Mar 21, 2013
Messages
32
I do this type of chart a lot in both forms & reports & its fairly simple to set up though you often need to tweak the scaling to get it to work

Attached are 3 screenshots from a report with several subreports with bar charts
- full report / report design & one of the subreports full size

This the full code to achieve the subreport shown with explanatory comments
Its more complex than you want but the idea is the same

Similar code is used in each subreport & in various forms

Code:
Dim ScalingFactor As Integer
Const CHARTWIDTH = 6  'multiplying factor to fit the space available
Const TwipsNumber As Integer = 567 (twips per cm)

Function GetScale()
'get size of largest value for chart
GetScale = Nz(DMax("CountOfPastoralRecordID", "qryPRecordsByFaculty", "PupilID = '" & Me.PupilID & "'"), 0)  
End Function

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

'Bar1 is the textbox used to create the bars in the chart
Me.Bar1.Width = CHARTWIDTH * TwipsNumber * Me.CountOfPastoralRecordID \ ScalingFactor

If Me.Bar1.Left + Me.Bar1.Width + TwipsNumber * 0.1 > (CHARTWIDTH + 0.5) * TwipsNumber Then
    'large value - show value in white font on top of bar
    Me.txtCount.Left = Me.Bar1.Left + Me.Bar1.Width - 1 * TwipsNumber
    Me.txtCount.ForeColor = vbWhite
Else 'small value - show value in black font to right of bar
    Me.txtCount.Left = Me.Bar1.Left + Me.Bar1.Width + TwipsNumber * 0.3
    Me.txtCount.ForeColor = vbBlack
End If

End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'add label to show maximum value fitting space available
'this is rounded up from max value used in chart
ScalingFactor = Round((GetScale + 5) / 10, 0) * 10
Me.LblScale.Caption = "(Full Width = " & ScalingFactor & ")"
End Sub


Hope that helps - feel free to ask questions if necessary

Thank you! Working on it now.. will reply with updates!
 

ginabenina

Registered User.
Local time
Today, 01:35
Joined
Mar 21, 2013
Messages
32
Code:
Function GetScale()
'get size of largest value for chart
GetScale = Nz(DMax("CountOfPastoralRecordID", "qryPRecordsByFaculty", "PupilID = '" & Me.PupilID & "'"), 0)  
End Function

If I am using a value instead of a count would I just have GetScale equal to that column from the query? I think maybe my query is not set up correctly. Everything is compiling.
My query has a list of areas, with values based on what status they are in. Each status is assigned a value between 1-100. Should I change the GetScale variable equal to the max value of 100?
 

isladogs

MVP / VIP
Local time
Today, 09:35
Joined
Jan 14, 2017
Messages
18,186
If the max possible value is always going to be 100, then set both GetScale and ScalingFactor to that value.

In fact just set scaling factor to 100 and remove GetScale

Or just hardcode 100 wherever either of those items are used...if that value will never change
 

ginabenina

Registered User.
Local time
Today, 01:35
Joined
Mar 21, 2013
Messages
32
Thank you ridders!
Just back to work after flu, still working this. I appreciate your help!!!
 

Users who are viewing this thread

Top Bottom