Solved

Access: formatting report fields based upon conditional statement

Posted on 2014-04-21
6
362 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

862 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

23 Experts available now in Live!

Get 1:1 Help Now