Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count conditional formating (2003)

    Hi, I've got a continuous form with 3 text boxes (A, B, and C) which contain dates fields. Each text box has conditional formatting set to change the font color to red under given conditions. I'd like to count the number of occurances on the form in which the text is red.....can this be done? Thanks, Van

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

    Re: count conditional formating (2003)

    There is no built-in way to check the result of conditional formatting, so you'll have to look at the conditions themselves. You can count the number of records that satisfy the conditions for each field, then add those counts. For example, let's say that the font for (the text box bound to) Field1 is red if Field1 > 20, and the font for (the text box bound to) Field2 is red if Field2 < 30. The count of records for which Field1 > 20 is

    =Abs(Sum([Field1] > 20))

    and that for which Field2 < 30 is

    =Abs(Sum([Field2] < 30))

    The total is

    =Abs(Sum([Field1] > 20)) + Abs(Sum([Field2] < 30))

  3. #3
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count conditional formating (2003)

    Hi Hans, I'm still having a bit of trouble. the control source for the bound text box for Field1(cpr) reads: =DateDiff("d",[cpr],Now()). The new count of records text box that you've helped me create reads: =Abs(Sum([text16]>335)). Unfortunately, the new text box just returns #error. thanks, Van

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

    Re: count conditional formating (2003)

    You can't sum a control. You can only sum expressions that refer to data from the record source:

    =Abs(Sum(DateDiff("d",[cpr],Now())>335))

    BTW do you really mean Now (i.e. current date AND current time) or are you interested in the date only? If the latter, use Date() instead of Now().

  5. #5
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count conditional formating (2003)

    Thanks muchly Hans, that clears things up. Regards, Van
    ps. yes, only need date()

  6. #6
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count conditional formating (2003)

    Hi again Hans, sorry to reply again after this thread has sort-of closed, but another question has occured to me. The total value has counted all the text formatted in red without a problem as you've shown me. What i'd like to do is have this number appear on another form (ie. the main switchboard) in the dB. I've had a few goes, but I'm no luck. Can u pls offer some advise? thanks again, Van

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

    Re: count conditional formating (2003)

    Say that the text box with the total on the form with conditional formatting itself is named txtCountRed. If you don't want to display the total there, you should still have this text box, but you can hide it by setting its Visible property to No.
    On the switchboard form, you can put a text box with control source

    =[Forms]![frmSomething]![txtCountRed]

    where frmSomething is the name of the form with conditional formatting. frmSomething must be open for this to work.

  8. #8
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count conditional formating (2003)

    that's what i've tried, but w/o frmSomething opened....frmSomething will be closed (ie. the secretaries view the main switchboard and open other forms as needs be). Can txtCountRed be shown w/o frmSomething opened? thanks, Van

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

    Re: count conditional formating (2003)

    Strictly, speaking, if frmSomething is closed, no text boxes have a red font... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    You could use a series of DSum expressions, or create a totals query to calculate the number.

  10. #10
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count conditional formating (2003)

    thanks Hans, thats what i did....create a totals query and all working fine now. cheers, Van

Posting Permissions

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