Conditional formatting based on matching record in separate query

[Steve Vincent]

I would like to conditionally format a patient's name (e.g., Bold) in a
report, based on whether it finds that name in a separate query of New
Admissions. How can I make my condition look for matching data in the query?
The table name is Today'sList, and the query name is Admissions. The
matching field name containing the patient's name is "Field1". Do I need to
construct an expression using IIf (I'm assuming)? My question is, somehow I
need my conditional expression to say "If Field1 matches any record's Field1
in this other query (Admissions), then make Field1 bold in my report."

Any help would be greatly appreciated.
TIA,
Steve

 

[Allen Browne]

Assuming Access 2000 or later, you can use conditional formatting. But first
you need to get a field from the Admissions query into the report so you can
test whether it is there or not.

Steps:
1. Create a new query, based on Today'sList and the Admissions query.

2. In the upper pane of query design, join Today'sList ot Admissions by
dragging your Field1 from Today'sList and dropping it onto the matching
field in Admissions. (You may find Access has done this for you, i.e. there
is already a line joining the 2.)

3. Double-click the join line. Access pops up a dialog offering 3 options.
Choose the one that says:
All records from Today'sList, and any matches from Admissions.

4. Add the key field from Admissions to the query.

5. Test: you should see all the records from Today'sList table. The key
field from Admissions should be blank where there is no record. Check to
make sure there are no duplicated records (which could occur if someone was
returned multiple times in the Admissions query.)

6. Save the query. Close.

7. Open the report in design view.

8. Change its Record Source property to this new query.

9. Select the text box that is to be bolded.

10. Choose Conditional Formatting on the Format menu.

11. Set up Condition 1 as:
Expression [xxx] Is Not Null
and choose Bold.

Alternative ways to look the field up could include DLookup():
http://allenbrowne.com/casu-07.html
or a subquery:
http://allenbrowne.com/subquery-01.html