Conditional formatting in Access Report based on string comparison

[fredmart]

Hi,

I'm having problems finding a way to apply conditional formatting based on comparing the text contents of two fields in two different tables.

What I'd like to do:
I've got two tables that are identically structured with about 50 fields each. The two tables are used for two data inputters to independently enter the same data from the same source as part of a validation check. I want to be able to check which if any fields are different across the two tables for each. I have therefore created a report that has each field from the two tables adjacent to each other so that I can visually compare them. This would be much easier if I could highlight only the ones that are different.

Database details:
Windows 7, Access 2013
The two tables I'm comparing are [Data extraction - final reconciled] and [Data extraction - Tazeem]
These have a one-to-one join using their primary key - [EPPI ID]
I've created a query that selects all fields in both tables, joined on [EPPI ID].
I've then created a report based on this query (which if it makes a difference has two subreports in it).

What I've tried:
I've tried various ways of comparing the two fields using conditional formatting based on an Expression, e.g. for the field [Unit of allocation - details] I've tried the following individual expressions:

 [Data extraction - final reconciled]![Unit of allocation - details]<>[Data extraction - tazeem]![Unit of allocation - details]

StrComp([Data extraction - final reconciled]![Unit of allocation - details],[Data extraction - tazeem]![Unit of allocation - details],1)<>0 

[Data extraction - final reconciled]![Unit of allocation - details] In ([Data extraction - tazeem]![Unit of allocation - details])=0

InStr([Data extraction - final reconciled]![Unit of allocation - details],[Data extraction - tazeem]![Unit of allocation - details])=0

None of these work when I use them as a conditional formatting rule in the report, yet they all return the values I expect when I add them as alias fields to the query the report is based on.

Any ideas why?

Also, as there are about 50 odd fields that I need to compare and would rather avoid clicking on each to set the conditional formatting, is there some code that will do this automatically for all fields in the report?

Any advice really appreciated!

Thanks,

Fred

[Rabbit]

Any ideas why?

I assume you're using the query for the source of the report. You need to reference the fields in that query. You can't reference a table that's in a query.

Also, as there are about 50 odd fields that I need to compare and would rather avoid clicking on each to set the conditional formatting, is there some code that will do this automatically for all fields in the report?

You can set them all at once by selecting them all. But the condition for them all would be the same. It sounds like you want the condition to be different each time and you can't do that automatically because there's no way for it to know what the condition will be.

[zmbd]

A thought.

Tbl1 left join Tbl2 on each field
Or() conditional for isnull() on each field
So, if any one of the fields does not match between the two table, then only those records are reported in the query. That alone should reduce your eyestrain :-)

Now if one could take the conditional formatting for each control such that if:
[nameofcontrol_1_a] <> [nameofcontrol_1_b]then format.

You would have to set the conditional for each pairing
[nameofcontrol_2_a] <> [nameofcontrol_2_b]then format.
[nameofcontrol_3_a] <> [nameofcontrol_3_b]then format.
etc...

Another thought...
One data entry table, which sound a tad un-normalized.
One data table for the verification - same format at tbl1 however only one record.
Form for the primary data entry.
Form for verification two subforms
2nd-User selects record in subform1 which is read-only
2nd-User enters data into subform2 on change event compares the field to the current-record in subform1. If not match then force 2nd-User to re-enter and confirm or abort, the change is made to tbl1 re-query the subform1 and reselect the record change is noted in log table for later review.
2nd-User selects new record subform1, tbl_verifcation fields are set to null subform2 required to update.
Some kinks to work out in the above.