Preventing duplicate names from two different fields appearing in a report

bearcub

Well-known Member
Joined
May 18, 2005
Messages
701
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
I have two fields in my report where they might sometimes contain the same name.

One of the fields is called RCC/SCC. The other field is called Directory office.

The report I'm running has the RCC/SCC field on top and the Directory offiice right below it in the detail area of the report.

Across from the RCC/SCC field, in the report, I have the Address field and right below the address field I have the City, and State.

If the directory office has something in the field, some of the RCC/SCC locations don't have a corresponding Directory office so it is left blank, then it will be displayed right beneath the RCC/SCC field.

Here is an example of what I don't want to see:

RCC/SCC: Eden
Directory office: Eden
Address: 123 Uptown Street,
City: NewHaven, Connecticut

The report should read

Code:
Eden               123 Uptown Street
                   New Haven, Connecticut

Instead, I'm getting this

Code:
Eden               123 Uptown Street
Eden               New Haven, Connecticut

I do have the hide duplicate property set to yes but because these are 2 different fields it isn't going to work.

Is there a formula that I could use which says if Directory office = Rcc/SCC then return nothing, otherwise - if they are different- display the Directory office text?

Thank you for your help,

Michael
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
As long as you use a calculated textbox that refers to the hidden DO control. Something like:
IIF([Directory office] = [Rcc/SCC],"",[Directory office])

Advise not to use any special characters (except possibly underscore) or spaces in your object names (if, as it appears, you really are).
 
Upvote 0
Thank you Micron, I applied your suggestion and it worked.

I inherited the database and I thought it was odd with the spaces, etc. I have difficulty sorting sometimes because of the column names.

Michael
 
Upvote 0
I have difficulty sorting sometimes because of the column names.
Michael
Don't know if you realize but you don't have to keep whatever Access provides for a label caption. You can change it to anything.
Good luck!
 
Upvote 0
I've been hesitant to change the field names in my tables because I was afraid my queries and reports would cease to work. If they won't be corrupted by doing this, I 'm change the field names to camel case.

would changing the field names now after the database has been field break anything I have in place?

Michael
 
Upvote 0
Most likely. While there is a default option called Name Autocorrect it's generally thought to contribute to db corruption so I haven't allowed it for some time, thus I don't recall how extensive it is. Likely it won't fix queries. You'll have to weigh the effort against what may be more about dealing with what you have and do better going forward vs starting over. Maybe also consider if others will see your work.
 
Upvote 0
I inherited the database and didn't make an effort to reinvent it, I just simplified it. I will play around with changing the field names and see what happens. Going forward, I'll just use one word to describe a field and, if necessary, use Camel case.

thank you again for the advice,

Michael
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top