Conditional Formatting Based On Multiple Possible Conditions

[DB_Queen]

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]

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]

You can use VBA, but it needs to be in the format event of the section containing txtStatus, presumably the detail section.

 

[Gasman]

My first thought is just a function to return required value?

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

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]

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]

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]

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]

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]

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]

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]

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")