I have a field that is a calculated text field, made up from other fields in another table. The calculation looks like this:

Pubs::Pub name & ", " & Pubs::Address & ", " & Pubs::Town & ", " & Pubs::County & ", " & Pubs::Country &" (" & Pubs::Operator & ")"

When all of the referenced fields are populated it produces a nice tidy entry like this example:

THREE GUINEAS, Station Hill, READING, Berkshire, England (FULLERS)

However, often a field (usually but not exclusively Pubs::Address) is blank. This is correctly blank, but produces:

THREE GUINEAS, , READING, Berkshire, England (FULLERS)

which is ugly. There must be a way to exclude this field, where it is blank, and the associated commas or quotation marks, but I am at a loss to come up with it.

Any help gratefully received

28 minutes ago, Simon Hall said:

a calculated text field, made up from other fields in another table.

I am confused by the "another table" part. What exactly is the relationship between this table (where the calculation takes place) and the Pubs table? If it is one-to-many, then your calculation will return a result made up from data in the first related pub only - which makes very little sense. If it's many-to-one, then why not make the calculation in the Pubs table where it can be performed once and stored - instead of performing it multiple times in the child table? Then it could be simply =

Substitute ( List ( Pub name ; Address ; Town ; County ; Country ) ; ¶ ; ", " ) & " (" & Operator & ")"

I am also not sure what you mean by "a calculated text field". Unless the result needs to be overwritten by user, this should be a Calculation field with the result type set to Text.

47 minutes ago, Simon Hall said:

and the associated commas or quotation marks,

What quotation marks? I don't see any quotation marks in your result.

 

Thanks for this, and apologies for not being more clear on some points. Taking your responses in reverse order:

Yes, for 'quotation marks' read 'brackets' - very sloppy on my part

Again, 'a calculated text field' should be 'calculation field with the result as text'

The first one is slightly more difficult to explain. For each entry in the 'Pubs' table there could be one or many entries in the 'Drinks' table. However, I think that I'm probably confusing you by not making clear that the Calculation field to which I refer is only to appear on the 'Drinks' layout. I don't want it stored anywhere and I have prevented user editing of the result on the 'Drinks' layout.

Hopefully this makes it more clear and, again, apologies and thanks for your patience

2 hours ago, Simon Hall said:

for 'quotation marks' read 'brackets'

To prevent the brackets from appearing when the Operator field is empty, change this part:

& " (" & Operator & ")"

to:

& If ( not IsEmpty ( Operator ) ; " (" & Operator & ")" )

I am afraid I don't know of a more elegant solution.

 

2 hours ago, Simon Hall said:

the Calculation field to which I refer is only to appear on the 'Drinks' layout.

You can easily place a field defined in the parent (Pubs) table on a layout of a child (Drinks) table. As I already mentioned, the advantage of defining it there is that it can be stored. This way it will recalculate only when one of the referenced fields is modified. If, OTOH, you define it in the Drinks table, it will be forced to unstored and it will recalculate on every screen refresh. This may not make a noticeable difference to you, but it will add a tiny amount to your electricity bill, your country's carbon footprint and this planet's temperature.

 

Edited August 25, 20214 yr by comment

Lovely. Thanks for all this. Understood on all counts. I use a similar function on another layout, so defining it in the 'Pubs' table and simply placing it wherever I need it would indeed be a better solution. And i learnt about 'List' and 'Substitute' too! Thanks again