Solved

Access: formatting report fields based upon conditional statement

Posted on 2014-04-21
6
374 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 36

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 36

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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying 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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

730 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