Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Format (Access 2k)

    I have a situation where I need more than the 3 conditons allowed in the conditional formatting on a form. I am using a continous form and I am displaying a couple of hundred records from a query. I want to highlight one field in the display under certain conditions. (There are 5 different conditions). Does anyone know at what point or event the conditional formatting is invoked? I have yet to determine this. The point of this formatting is to draw the user's attention to particular records in the display so to do this as the line has focus will not work.

    thanks,
    Tom

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Conditional Format (Access 2k)

    Do you want a different formatting for each of the 5 conditions, or do you just want to highlight the field if any of the 5 conditions are met? If the latter, then in your conditional formatting, select "Expression Is", then put in an equation using OR, such as " State='FL' OR State='GA' OR State ='AL' ".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Format (Access 2k)

    I am dealing with a percentage calculation. I need to highlight by bolding the text if the percentage is less than 5%. I need to change the colour if the percentage is greater than 7%. If the percent is 0 then make the text red if the percentage is between 2% and 4% change the background to yellow. If the percentage is less than 0 then make the background green.

    Tom

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Format (Access 2k)

    I was messing about with this myself the other day.
    What I found was (To Me) unexpected.
    I have a calculated field which works like, [Field1]/[Field2] and wanted the text highlited if the answer was <100%.
    I entered this in the C/Format and didn't work, the text was unchanged.

    I changed it to <1 and it worked. The text changed to Red in my case, If >1 then text =green.

    Try <0.05 in your case, it may work.

    Dave

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

    Re: Conditional Format (Access 2k)

    Tom,

    You can combine conditional formatting for the text with a curious technique for the background to obtain more than 3 different formats. I found the background technique in <post#=93232>post 93232</post#> (it works in older versions of Access too).

    It's based on the idea that the Format property for a numeric value can have up to 4 different sections: for positive numbers, negative numbers, zero and null.

    If the record source for your form is a table, create a query based on that table and use that as record source instead.

    Open the query that acts as record source in design mode. Add a field, say Dummy, that returns 1, -1, 0 or Null depending on the percentage value.

    For instance:

    Dummy: Switch([PercValue]<0,1,[PercValue] Between 0.02 And 0.04,-1,[PercValue]>0.07,0)

    Now, on the form, put a text box (without label) bound to the Dummy field. Set the font to WebDings and the font size to a large value, e.g. 72.
    Set the Format property to

    "gggggggggggggggggggggggg"<!t>[Green]<!/t>,"gggggggggggggggggggggggg"<!t>[Yellow]<!/t>,"gggggggggggggggggggggggg"<!t>[Red]<!/t>

    "g" is a box character in the Webdings font.

    Position and size this text box to cover the area you want to have a changing background color, and send it to the background. Set the background style of the other text boxes to transparent.

    The result will be

    <table border=1><td valign=top>Value</td><td valign=top>Dummy</td><td valign=top>Background</td><td valign=top>< 0%</td><td align=center valign=top>1</td><td valign=top>Green</td><td valign=top>Between 2% and 4%</td><td align=center valign=top>-1</td><td valign=top>Yellow</td><td valign=top>> 7%</td><td align=center valign=top>0</td><td valign=top>Red</td><td valign=top>Otherwise</td><td align=center valign=top>null</td><td valign=top>(default background color)</td></table>
    You can combine this with conditional formatting of the text boxes "on top" to show various combinations of background color and text color/style. You'll have to work out the exact conditions - you mentioned 5 in your post, but I seem to count 7 different ranges (x<0%, x=0%, 0%<x<2%, 2%<=x<=4%, 4%<x<5%, 5%<=x<=7%, x>7%).

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

    Re: Conditional Format (Access 2k)

    Hi Dave,

    The reason for what you mention is that percentages involve multiplication/division by 100.
    If a number is displayed as 50%, the number actually stored is 50/100 = 0.5.
    Or conversely, a number stored as 0.07 (that is, 7/100) is displayed in percentage format as 7%.

    Tom's problem is that conditional formatting allows up to three different conditions, while he needs to distinguish at least five different conditions.

    Regards,
    Hans

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Format (Access 2k)

    Hans
    Yes I see your point, I've never had to C/Format beyond three variants, but obviously may have to in the future.

    your wisdom is noted once again.

    " ps " " Whats national Swiss Day all about"

    Dave

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

    Re: Conditional Format (Access 2k)

    Dave,

    August 1 is the Swiss national holiday. See <post#=164196>post 164196</post#>.

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Format (Access 2k)

    Well all I can say to this is:

    Have an absolutely wonderful Swiss Day to all you Swiss Folk.
    p.s Have a beer for me
    If you're not Swiss, have a wonderful day anyway.

    Dave

  10. #10
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Format (Access 2k)

    Thanks Hans, I will try your suggestion.

    Tom

Posting Permissions

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