CODEWITHSUNDEEP
databasems-accessvba
Alex
Alex

Reputation: 6655

Combining fields, some null, in a textbox on a report in access

I'm trying to amalgamate a few address feilds into one text box on a report:

=[City]+", "+[County]+", "+[Post Code]

However not all records have an entry in the [County] column, which means that nothing shows in the textbox at all for these records. So I tried an Iif statement:

=IIf([County],[City]+", "+[County]+", "+[Post Code],[City]+", "+[Post Code])

This didn't work, how can I make the text box show whatever fields are present?

Upvotes: 3

Views: 4161

Answers (5)

HansUp
HansUp

Reputation: 97111

You can take advantage of the fact that the two concatenation operators (+ and &) handle Nulls differently. 

? "A" + Null
Null
? "A" & Null
A

So you could do this ...

? "A" & ", " & "B" & ", " & "C"
A, B, C
? "A" & ", " & Null & ", " & "C"
A, , C

... but if you don't want two commas when you have Null instead of the second string value, do this instead:

? "A" & (", " + Null) & ", " & "C"
A, C

If that all makes sense, apply the same pattern to your text box control source:

=[City] & (", " + [County]) & ", " & [Post Code]

You don't need functions (IIf, IsNull, Len, and/or Nz) to get what you want here.

Upvotes: 1

Daniel
Daniel

Reputation: 13132

Easiest solution IMO: Use Nz.

=[City]+", "+Nz([County]+", ")+[Post Code]

Though you may want ot use & instead of +. In Access + means summation, but & means concatenation.

Upvotes: 6

bonCodigo
bonCodigo

Reputation: 14361

Try this please: Rather super ugly thought... When Chr(13) and Chr(10) are there it will never be empty.

IIf(Isnull([County]), 
    IIf(Isnull([Post Code]), 
        IIf(IsNull[Post Code]), "No Address", [Post Code]),
        [City] + Chr(13) & Chr(10) +[Post Code]),
[Country] + Chr(13) & Chr(10) + [City] + Chr(13) & Chr(10) +[Post Code])

Upvotes: 0

SeanC
SeanC

Reputation: 15923

you were close with your IIf statement.

You are missing one of 2 possible tests

if only nulls can appear,

=IIf(IsNull([County]),[City]+", "+[County]+", "+[Post Code],[City]+", "+[Post Code])

if it can be empty ("") or null

=IIf(len(""&[County]),[City]+", "+[County]+", "+[Post Code],[City]+", "+[Post Code])

Upvotes: 1

tbur
tbur

Reputation: 46

You were so close!

=[City]+", "+[County]+", "+[Post Code]

should be:

=[City]&", "&[County]&", "&[Post Code]

Remember, a NULL trumps everything in math, so using the plus operator was giving you a NULL result every time any field was blank.

Upvotes: 3

Related Questions