If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional formatting based on matching record in separate query
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 |
#2
|
|||
|
|||
Conditional formatting based on matching record in separate query
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 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Steve Vincent" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|