Conditional Formatting Based On Multiple Possible Conditions (1 Viewer)

DB_Queen

New member
Local time
Today, 14:03
Joined
Sep 20, 2021
Messages
22
Hello experts.

I have a report that I am trying to create that shows someone's work status on a weekly basis. It is grouped by employee and the query pulls all records in the associated table based on a date range entered by the user.

I have two text boxes in the group footer that display a count of records in the detail section - let's call them txtONE and txtTWO - and a checkbox on group header that will be either TRUE or FALSE depending on the employee's schedule type.

I have a third unbound text field in the group header - let's call it txtSTATUS - that I would like to display text based on the following:

If the checkbox is TRUE, THEN
If the value of txtONE is =>3, txtSTATUS will display "Out of the Office"
If the value of txtTWO is =>3, txtSTATUS will display "Teleworking"
Otherwise txtSTATUS will display "In The Office"

If the checkbox is FALSE, THEN
If the value of txtONE is =>1, txtSTATUS will display "Out of the Office"
If the value of txtTWO is =>1, txtSTATUS will display "Teleworking"
Otherwise txtSTATUS will display "In The Office"

I have never created a conditional formatting formula this complex before. I attempted to use VBA to accomplish this but it only applies the code to the first group of the report and then repeats it for every subsequent group.

Appreciate any feedback or advise you have to share. Thank you :)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Feb 19, 2013
Messages
16,553
this isn't really conditional formatting which is basically about colour of font and back colour.

sometimes helps to work back from the other end - so you need a basic formula which results in three possible outcomes

'out of office' will be displayed if checkbox is true and txtOne>=3 or checkbox is false and txtOne>=1
'teleworking' will be displayed if checkbox is true and txtTWO>=3 or checkbox is false and txtTWO>=1
otherwise 'In the office' will displayed (which is the default)

so we have

=iif((checkbox and (txtONE>=3)) OR (not checkbox and (txtONE>=1)),"out of office",iif((checkbox and (txtTWO>=3)) OR (not checkbox and (txtTWO>=1)),"teleworking","In the office"),"in the office")
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:03
Joined
Aug 30, 2003
Messages
36,118
You can use VBA, but it needs to be in the format event of the section containing txtStatus, presumably the detail section.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:03
Joined
Sep 21, 2011
Messages
14,046
My first thought is just a function to return required value?

Can be done via CF as well.?
I just tried this quick test

1641482697213.png


Code:
Public Function SetCF(varField As Variant)
If Nz(varField, 0) > 8 Then
    SetCF = True
End If
End Function

Obviously just a simple test, but you should get the idea?
 

DB_Queen

New member
Local time
Today, 14:03
Joined
Sep 20, 2021
Messages
22
this isn't really conditional formatting which is basically about colour of font and back colour.

sometimes helps to work back from the other end - so you need a basic formula which results in three possible outcomes

'out of office' will be displayed if checkbox is true and txtOne>=3 or checkbox is false and txtOne>=1
'teleworking' will be displayed if checkbox is true and txtTWO>=3 or checkbox is false and txtTWO>=1
otherwise 'In the office' will displayed (which is the default)

so we have

=iif((checkbox and (txtONE>=3)) OR (not checkbox and (txtONE>=1)),"out of office",iif((checkbox and (txtTWO>=3)) OR (not checkbox and (txtTWO>=1)),"teleworking","In the office"),"in the office")
This looks promising, but I'm getting a "The expression you entered has a function containing the wrong number of arguments" error when attempting to set this as the control source for my unbound field....
 

DB_Queen

New member
Local time
Today, 14:03
Joined
Sep 20, 2021
Messages
22
You can use VBA, but it needs to be in the format event of the section containing txtStatus, presumably the detail section.
I tried this in both the header and the footer section of the group, but it does not work. txtStatus is not in the detail section, because it would repeat for every record in that section, and I only want it to appear once for each employee.
 

GPGeorge

Grover Park George
Local time
Today, 11:03
Joined
Nov 25, 2004
Messages
1,776
I tried this in both the header and the footer section of the group, but it does not work. txtStatus is not in the detail section, because it would repeat for every record in that section, and I only want it to appear once for each employee.
There is a property for controls on reports called "Hide Duplicates". Would that resolve the problem?
 

DB_Queen

New member
Local time
Today, 14:03
Joined
Sep 20, 2021
Messages
22
I tried this in both the header and the footer section of the group, but it does not work. txtStatus is not in the detail section, because it would repeat for every record in that section, and I only want it to appear once for each employee.
Update - when I put the report in Print Preview, this did work! Thank you so much!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:03
Joined
Aug 30, 2003
Messages
36,118
I should have mentioned that the format event doesn't fire in report view, just preview and print. Glad it worked for you.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:03
Joined
Feb 19, 2002
Messages
42,971
I would make a function to return the words you want to display and put it in the query so you can bind the result to a control. That means it doesn't matter whether this is a form or report or how the report is opened.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Feb 19, 2013
Messages
16,553
This looks promising, but I'm getting a "The expression you entered

sorry, typo on my part - copy/pasted too much

=iif((checkbox and (txtONE>=3)) OR (not checkbox and (txtONE>=1)),"out of office",iif((checkbox and (txtTWO>=3)) OR (not checkbox and (txtTWO>=1)),"teleworking","In the office")
 

Users who are viewing this thread

Top Bottom