Change field background color based on query results

[tagteam]

! would like to change the background color of a field that is the result of a Unique Values query. I am trying to get a list of invoices where all the line items are approved. I cant seem to get it to work the way I want because if even one invoice line item is approved it will show up as approved.

Is there a way to change the background color of the invoice field to red if ANY of the Approved line items are = False

[orange]

You could check Conditional Formatting on a Form. This may help.

[tagteam]

Yes, thank you. I am aware of conditional formatting but I am not sure the formula to get it to work correctly. I have 20 lines associated with an invoice, IN1234, and each line has an approval checkbox. I want the box to show red if there are any checkboxes that have NOT been checked.

So the query that produces the results on the continuous form is a DISTINCT query but I need to be able to check each line item that is tied to that Individual Invoice and I am not sure how to do that.

[orange]

I would suggest looking at the InvoiceDetail.

something along this line of thinking: Find those Invoices where the detail has an unchecked checkbox, then find all of the invoices that are not in the first list.

Select invoiceID from InvoiceDetail
where InvoiceID NOT IN
(Select invoiceID from InvoiceDetail
  where ApprovalCheckBox = False)

[June7]

Can use domain aggregate functions. Beware they can be slow.

DCount("*", "tablename", "checkbox=False And Invoice='" & [Invoice] & "'")

Can be in query or textbox or in Conditional Formatting expression. Then Conditional Formatting can set background color if the count is <> 0.

[tagteam]

Thanks Orange, I was able to use a query June 7 gave me to make only the invoices I want appear so I dont need to make the ones I dont want red anymore

[June7]

Actually, I just gave you a domain aggregate expression. The query suggested by orange might be more efficient as a form or report RecordSource, although I have known the NOT operator to have issues.

[tagteam]

I was refer to the help you gave me in the thread https://www.accessforums.net/access/...ail-44767.html

For those looking, I used the query from the thread above to only show invoices that were fully approved so I did not end up needing the conditional formatting to highlight an invoice that was not fully approved.

Thanks