Access: formatting report fields based upon conditional statement

I am trying to compare all fields in table1 to the corresponding fields in table2.

I have a query set up to match the tables on an ID field.

What I want to do is compare the fields in the report and formatting differences in the output.  I think I need to use the Detail.Print or On.Format event - but not sure how to code it.

If field1 <> field2 then highlight background of field2.

Thanks for the help.
je
aeolianjeAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
go back to my original post.  The best solution is to eliminate the records from the query if they have no differences.  I don't see any reason why the query you posted wouldn't work.  I will repeat - unless you are POSITIVE that you have no columns with nulls, then you MUST use the Nz() function that I posted.

If you want to eliminate the records after the fact, you can do it in the Format event of the detail section.  In order to not code a massive nested If, I would just count the errors.

DiffCount = 0
If Me.fld1 <> Me.fld1a then
    DiffCount = DiffCount +1
End If
If Me.fld2 <> Me.fld2a Then
    DiffCount = Diffcount +1
End If
If Me.fld3 <> Me.fld3a Then
    DiffCount = DiffCount +1
End If
....
If DiffCount = 0 Then
    Cancel = True    '' this will cancel the print
    Exit Sub
End If

Open in new window

0
 
PatHartmanCommented:
If you want the report to show only records with differences, you will need to add criteria to the query.

Select tbl1.*, tbl2.*
From tbl1 Inner Join tbl2 ON tbl1.pk = tbl2.pk
Where
Nz(tbl1.fld1, "") <> Nz(tbl2.fld1,"")
OR  Nz(tbl1.fld2, "") <> Nz(tbl2.fld2,"")
OR  Nz(tbl1.fld3, "") <> Nz(tbl2.fld3,"")
OR  Nz(tbl1.fld4, "") <> Nz(tbl2.fld4,"")
OR ......

I used the Nz() function just in case any of the columns are null since comparing something to null ALWAYS return false so those records would not trigger a "true".

Then, you can use conditional formatting.  But, if you think you have any nulls, use the Nz() there also.
0
 
aeolianjeAuthor Commented:
I can get it to work for one field -- but when I add another, it give not results.  (see below).

Also - this will work great to identify all records that have differences -- but I want to be able to highlight the differences on a report.

SELECT tbl_input_compare_1.Platform, tbl_input_compare_1.RACFID, tbl_input_compare_1.LNAME AS LNAME1, tbl_input_compare_1.MI AS MI1, tbl_input_compare_1.FNAME AS FNAME1, tbl_input_compare_1.Branch AS Branch1, tbl_input_compare_1.Dept AS Dept1, tbl_input_compare_1.Direct_Report AS Direct_Report1, tbl_input_compare_1.Direct_RepID AS Direct_RepID1, tbl_input_compare_1.Alt_app AS Alt_app1, tbl_input_compare_1.Alt_Appid AS Alt_Appid1, tbl_input_compare_1.App_name AS App_name1, tbl_input_compare_1.A1 AS [A1-1], tbl_input_compare_1.A2 AS [A2-1], tbl_input_compare_1.A3 AS [A3-1], tbl_input_compare_1.A4 AS [A4-1], tbl_input_compare_1.A AS [A-1], tbl_input_compare_1.SourceFile AS SourceFile1, tbl_input_compare_1.SelectedApp AS SelectedApp1, tbl_input_compare_2.*
FROM tbl_input_compare_1 INNER JOIN tbl_input_compare_2 ON tbl_input_compare_1.RACFID = tbl_input_compare_2.RACFID
WHERE (([tbl_input_compare_1].[platform]<>[tbl_input_compare_2].[platform])) OR (([tbl_input_compare_1].[RACFID]<>[tbl_input_compare_2].[RACFID]));

Thanks for the help.
je
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
aeolianjeAuthor Commented:
ahhh -- I just found the conditional formatting option -- never used it before -- I used to code it..... very useful....

je
0
 
aeolianjeAuthor Commented:
The conditional formatting is working -- THANKS!

Is there a way to print only the rows that meet the conditional formatting?

je
0
 
aeolianjeAuthor Commented:
This worked great!  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.