Omitting a joined field when empty of data

[stealth3]

I have a report where in the Page Header section, I have a field called Title with the Control Source '[Title]& ": "&[Subtitle]'. The Subtitle is not in the report but accessible via the query. However sometimes the Subtitle field is empty, and less frequently but ocassionally the Title field is empty.

There are two parts to my query:
1. How can I have the colon ) not appearing in the Title field when the Subtitle field is empty, and
2. What code do I need (On Format) to make the Title field Not Visible when it's empty?

I have used the following code for other fields, and it works:

If IsNull(Author) Then
Me.Author.Visible = False
Else
Me.Author.Visible = True
End If

Thank you

 

[mp9]

In answer to the first part of your question, change the ControlSource of the Title field to something like:

=[Title] & IIf([Subtitle] Is Not Null,": " & [Subtitle])

Doesn't the code you've used for controlling visibility work for the title field then? As an aside, I think the example code you gave can be simplified as:

Me.Author.Visible = Not IsNull(Author)

Hope this helps.

http://www22.brinkster.com/accessory/

 

[stealth3]

Thank you mp9

The simplified code works and thank you.
However there is something missing from the controlsource. I've tried a few things but they aren't working eg
=[Title] & IIf([Subtitle], Is Not Null, ": ") & IIf([Subtitle], IsNull, ???}

I don't know whether I need to repeat the IIf statement & and I don't know what, if anything should come after IsNull.

Any ideas.

Thank you

 

[mp9]

=[Title] & IIf([Subtitle] Is Not Null,": " & [Subtitle])

should give you exactly what you need, i.e.

Just Title when subtitle is null
Title: subtitle when subtitle isn't null

At least it works in the noddy database I knocked up to test it.

http://www22.brinkster.com/accessory/

 

[stealth3]

Dear mp9

I have copied your version completely into my database and it's still not working. I've also tried several variations and receive the following error messages:

The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier; and

You (may) have entered an operand without an operator.

Should the criteria 'Is Null' be in the Subtitle field of the query?

Thank you again

 

[mp9]

To work out what's causing the problem, try each piece at the ControlSource on it's own, i.e.

First, just try:
=[Title] & ": " & [Subtitle]

Then try:
=IIf([Subtitle] Is Not Null, ": " & [Subtitle])

If both these work, there's no reason why you can't combine them as:
=[Title] & IIf([Subtitle] Is Not Null, ": " & [Subtitle])

If this still doesn't work, check field names ("Title" and "Subtitle" presuambly, no spaces?), data types (both text strings?)... beyond that, I don't know what else to suggest. I've just recreated your scenario in a database here and the ControlSource I've suggested works. Not that it should make a difference, but what version of Access are you using?

http://www22.brinkster.com/accessory/

 

[stealth3]

Again thank you but it isn't working. But the Title and Subtitle fields are memo types. I don't know if that would make a difference.

There must be something wrong with my report, table etc. I'm still getting the same type of error messages.

If I ever figure it out, I'll post you a message.

cheers

 

[mp9]

Ah, it's because they're Memo types I'm afraid. If they were plain Text fields this would work.

http://www22.brinkster.com/accessory/

 

[stealth3]

However after further trial and error, the following worked:
[Title] & IIf([Subtitle] Is Not Null, ": ") & [Subtitle]

The closing bracket was in the wrong place.

Thanks for your help, it helped me get there.

cheers