Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: NEED horizontal bar based on field value in report detail section

    I have a field based on a query in the detail section of an Access (2003) report. It displays a number.

    The number value itself will not show in this field. The font will be the same color as the color of the background.

    I wish to have this field expand or collapse (graphically via colored bar) to the right (horizontally) if the number gets larger or smaller.

    See the .pdf attachment.

    Thanks . . . Rick
    Attached Files Attached Files
    Last edited by Rick Schreiber; 06-03-11 at 00:16. Reason: Attachment

  2. #2
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Select Case

    I like this but it doesn't seem to work.

    I found this here: Access VBA to Change colour of cells in Report - MrExcel Message Board

    At the top of the VB window I selected Detail on the left and Format on the right.

    Code:
    Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
    Select Case [txtBAR]
        Case Is < 40
            [txtBAR].BackColor = 255 'red
        Case Is < 60
            [txtBAR].BackColor = 52479 'orange
        Case Is < 80
            [txtBAR].BackColor = 65535 'yellow
        Case Is < 100
            [txtBAR].BackColor = 65280 'light green
        Case 100
            [txtBAR].BackColor = 32896 'dark green
    End Select
    End Sub

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how different is this question to the earlier one you posted
    http://www.dbforums.com/microsoft-ac...d-percent.html
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    did you look at 'holidaychart.zip' ?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    The difference is . . .

    At first I was using a textBox for the expanded bar based on a number from another field in the detail section.

    Now I'm trying to use the same different field placed in the detail section for the expanded bar. Should be easier to make work. (Not!)

    YES! I did download the the "holidaychart.zip."

    It didn't seem to apply to what I'm trying to accomplish and was somewhat confusing to me.

    Five tables - four queries and one report!

    When I preview the report I see "Playing Staff" as a y scale and "November 2006" as an x scale but the report itself empty?

    I don't get it.

    Rick

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    granted as an example it doens't meet your requirement, but then Im not going to waste a huge amount of time whn I have work pressures of my own to roll a 'perfect' example for you. After all you've been developing this application for a while now, you should have some development (design and debugging) skills to apply.

    the report should have looked like the attachment

    whether its 5 tables, 4 queries and one report is largely irrelevant
    what is relevant is that the report changes colours on the fly dependent on the underlying data. so if you looked at the code you should be able to see how the colours are changed.

    having established that you then can modify the width of one of the coloured boxes as required by applying a scaling factor

    say you elect to specify 100% as 10cm
    and you'd worked out that the target value to reach that 100% was 48
    then you need to change the width of the box to map to that scaling

    mycontrol.width = (actualValue / targetvalue) * targetwidth
    where
    targetwidth = the nominal width of the 100% wide box
    targetvalue = the theoretical 100% value...
    actual value is the actual value retrieved from the data.
    so assuming the target width is 10cm
    the the target value is 48
    and actual value of 24 would derive a box width of 5cm
    av of 72 would derive a box width of 15cm

    you would need to put some upper limit so the box doesn't over flow
    you could find the maximum printable area by subtracting the box.left position from the reports width (and you may need to take into account the reports margins as well).
    you also should consider what to do if the actaul value is zero or woudl come close to zero (i'd suggest hiding the box in such cases, but if you do that then you need to unhide when non zero).
    Attached Thumbnails Attached Thumbnails holidaychart.png  
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Rick Schreiber View Post
    I like this but it doesn't seem to work.
    so did you you find out why your code didn't seem to work?

    did you get an error message

    did you try to work out what that error message was telling you

    did you try to step through the code where it went wrong to work out what the problem was.

    my guess would be that you were not referring to a specific value in the select case statement
    you'd probably need to expressly convert it to datatype integere

    eg
    Code:
    select case cint(txtbar.value)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •