Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Condtional Formatting (Access 2000)

    Having built a report dynamically from a crosstab query I would like to apply conditional formatting to the results in particular change the back color for negative numbers to red. I assume that you need to use the onprint event but cannot see how to do so. Can anyone help please.

    Mitch

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Condtional Formatting (Access 2000)

    You can add conditional formatting in code when you build the report.

    Dim ctl As Control
    Set ctl = ...
    ctl.FormatConditions.Add(acFieldValue, acLessThan, 0).BackColor = vbRed

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Condtional Formatting (Access 2000)

    Hi Hans

    Thansk once more I do apologise for all these posts but I seem to have bitten off more than I can chew. Anyway tried as you suggested (I hope) but it did not produce the result I hoped for.

    The section of the code I used is as follows;

    For n = 0 To var - 1 'Set up fields in report
    var1 = qdf.Fields(n).Name 'Get field name
    Set ctltext(n) = CreateReportControl(Reports(0).Name, acTextBox, acDetail, , var1, l, 0, w, 300) 'Enter Controls for details
    ctltext(n).FormatConditions.Add(acFieldValue, acLessThan, 0).BackColor = vbRed
    l = l + w + Space1 'Repositions Left
    w = 800 'Width text box
    Next

    The report itself is fine and it does have negative numbers but they do not appear in print preview as red background. The ctlText(n) is dimensioned as an array at the start of the function.

    Thanks

    Mitch

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Condtional Formatting (Access 2000)

    I tested this in Access 2002, and it works correctly there. Perhaps someone with Access 2000 can test it.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Condtional Formatting (Access 2000)

    Can you test that any of your values actually meet the condition required for the conditional formatting?

    Might it be possible that the underlying query has converted the numbers into strings?

    Whenever I use NZ in a query the field becomes a string, for instance. I need to explicitly turn the string back into a number using the Val function if I want to treat the result as a number.
    Regards
    John



  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Condtional Formatting (Access 2000)

    I test Hans' sugestion :
    ctltext(n).FormatConditions.Add(acFieldValue, acLessThan, 0).BackColor = vbRed
    in Access 2000 and it work for me.
    Francois

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Condtional Formatting (Access 2000)

    Hans and Francois

    First my thanks for all your help. This very strange, when I run my code with your suggestion included exactly and open the report in preview (last step in that code block) the negative numbers are not formatted to Red. However if I then switch the report to design view click on a detail column then select Tools Conditional formatting from the toolbar it shows that if the number is less than zero it should be Red. If I then click off that the box I have just been on is white (in design view) when I switch to preview the negative numbers in that column are Red. However negative numbers in other columns are still not in Red. Does this mean there is a step I have to take to make sure that the command is active? For instance is there some way you turn conditional formatting on?

    Thanks

    Mitch

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Condtional Formatting (Access 2000)

    I'm sorry, no idea. Conditional formatting should take effect immediately, and it does for me (I tested it on controls created in code, in an array, the way you do).

    If you wish, you can post a stripped down and zipped copy of the database; see <post#=401925>post 401925</post#> for instructions.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Condtional Formatting (Access 2000)

    Hans

    Thank you for your last post, I apologise for the delay in acknowledging it. I have spent some time stripping out the database so as I could send it to you. That has now been done and it is attached.

    I have set up a switchboard, which will ask for the year and open the report in design view. You will see that none of the detail summary text boxes are highlighted, as they are when you set conditional formatting from the Toolbar.

    Originally I had set all the totals to bold face and I noticed that in the one month that the total was negative the total was in ordinary face. So I switched the detail section to bold face and you can see when you review the form in preview that anything that is negative is not shown in boldface. I do not know what this means but it does suggest that the conditional formatting applied as you told me has had some effect. I have used a with loop and the sequence that the instructions appear does not make any difference. By the way there is in the database records data for 2005 and 2004 but the latter is the only one with negative results.

    I look forward to hearing from you and really appreciate your help. I am traveling at the moment and cannot try this on a different machine (in case my version of access is corrupted in some way).
    Regards

    Mitch

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Condtional Formatting (Access 2000)

    The text boxes are created with Back Style = Transparent, so the background color never shows. Add the following line in both loops that create text boxes, within the With ... End With part:

    .BackStyle = 1

    (0 = Transparent, 1 = Normal)

    Note 1: Apparently, you don't require explicit declaration of variables. I strongly recommend that you do so:
    <UL><LI>Select Tools | Options... in the VBE.
    <LI>Activate the Editor tab.
    <LI>Tick the check box "Require Variable Declaration".
    <LI>Click OK.
    <LI>Add the line

    Option Explicit

    to the top of all your existing modules.[/list]You'll get error messages at first, because several variables haven't been declared.
    See for example <post#=314748>post 314748</post#> for a short explanation why you should do this.

    Note 2: To avoid confusion between ADO and DAO, declare rst as a DAO recordset:

    Dim rst As DAO.Recordset

    Note 3: The SetupDetail function uses a variable Space1, but it isn't declared and it isn't assigned a value.

  11. #11
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Condtional Formatting (Access 2000)

    Hans
    You are a genius than you very much. I also would like to highlight values that are blank or I guess more correctly null. I assume I replace aclessthan with acexpression, but I am unsure how I define the variable can you advise please.

    Thanks

    Mitch

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Condtional Formatting (Access 2000)

    Try something like this:

    With ctlText(n)
    .FontBold = True
    .BackStyle = 1
    .FormatConditions.Add(acFieldValue, acLessThan, 0).BackColor = vbRed
    .FormatConditions.Add(acExpression, , "IsNull([" & var1 & "])").BackColor = vbYellow
    End With

    Of course, you can modify the actual formatting.

  13. #13
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Condtional Formatting (Access 2000)

    Thansk Hans I will try that.

    Regards

    Mitch

Posting Permissions

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