Preventing duplicate names from two different fields appearing in a report

[bearcub]

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

Eden               123 Uptown Street
                   New Haven, Connecticut

Instead, I'm getting this

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

 

[Micron]

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).

 

[bearcub]

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

 

[Micron]

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!

 

[bearcub]

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

 

[Micron]

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.

 

[bearcub]

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