Non-display of certain fields in a report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dominick D. - 28 Apr 2008 15:13 GMT

Hello, All

I have a report that looks like this:

SAP DB2
1234 1234
McDonalds McDonalds
Wendy's Taco Bell

Basically, I am requested to show ONLY THOSE FIELDS THAT DO NOT MATCH. So,
when the above report displays, it should only show fields that are not
equal. So, for example, you have McDonalds in the SAP column and you have
McDonalds in the DB2 column. Since they match, I don't want either column to
display any data; basically, show nothing or nulls in those columns. Would I
use the iif function to filter out matching fields to show only non-matching
fields? Thanks.

Reply to this Message

Bill - 28 Apr 2008 15:45 GMT

Just filter your query or RecordSet "WHERE SAP <> DB2"

> Hello, All
>
[quoted text clipped - 15 lines]
> non-matching
> fields? Thanks.

Reply to this Message

John Spencer - 28 Apr 2008 16:01 GMT

If those are the only columns then filter them out with a where clause

If those are only part of the data, you can return nulls in the query using an
IIF statement to calculate the values to be displayed.

Field: ShowSap: IIF (SAP<>DB2,SAP,Null)

Field: ShowDB2: IIF (DB2<>SAP,DB2,Null)

YOu can also use code in the report's detail event to hide or show the
associated control

If Me.SAP = Me.DB2 Then
Me.Sap.Visible = false
Me.DB2.Visible = False
ELSE
Me.Sap.Visible = True
Me.DB2.Visible = True
END IF

IF the controls have borders, you will have slightly different results based
on which method you use. Doing it in the query, the borders will still show
in the report, doing in with the code above will hide the control (and
therefore the associated borders on the control).

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> Just filter your query or RecordSet "WHERE SAP <> DB2"
>
[quoted text clipped - 17 lines]
>> non-matching
>> fields? Thanks.

Reply to this Message

Dominick D. - 29 Apr 2008 13:26 GMT

Hi John and thanks for your assistance. Basically, here's how the report
looks like:

SAP DB2 Data Comparison

DB2 DATA SAP DATA

Cust_Nam Wendy's Con_Legacy_Name2 McDonalds

FIELD2 <DATA> FIELD2 <DATA>
FIELD3 <DATA> FIELD3 <DATA>

Now, I need to be able to show only those matching fields, as shown above.
If the data from the db2 data column doesn't match the data from the sap data
column, I need to not show the field name and the associated data. So, given
the above, since Wendy's does not match McDonalds, I would not show the field
names Cust_Nam and Con_Legacy_Name2 and the data. It seems that I need to do
some coding, but I don't know VBA or VB. Is this is what is needed? Or do I
have to somehow manipulate the control properties so that I hide non-matching
data from both db2 data and sap data columns? I'd appreciate it if you could
help me out here.

> If those are the only columns then filter them out with a where clause
>
[quoted text clipped - 47 lines]
> >> non-matching
> >> fields? Thanks.

Reply to this Message

Dominick D. - 29 Apr 2008 13:42 GMT

John, excuse me; regarding my last reply, if both fields match, then don't
show the field names or data, so if DB2 DATA has McDonalds and SAP DATA has
McDonalds, do not show the field names and the data.

> If those are the only columns then filter them out with a where clause
>
[quoted text clipped - 47 lines]
> >> non-matching
> >> fields? Thanks.

Reply to this Message