Solved

Access: formatting report fields based upon conditional statement

Posted on 2014-04-21
6
371 Views
Last Modified: 2014-04-22
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
0
Comment
Question by:aeolianje
  • 4
  • 2
6 Comments
 
LVL 35

Expert Comment

by:PatHartman
ID: 40013168
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
 

Author Comment

by:aeolianje
ID: 40013385
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
 

Author Comment

by:aeolianje
ID: 40013492
ahhh -- I just found the conditional formatting option -- never used it before -- I used to code it..... very useful....

je
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:aeolianje
ID: 40014710
The conditional formatting is working -- THANKS!

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

je
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40014885
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
 

Author Closing Comment

by:aeolianje
ID: 40015640
This worked great!  Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question