Solved

Access: formatting report fields based upon conditional statement

Posted on 2014-04-21
6
357 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 34

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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 34

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now