在访问中的报表的文本框中组合字段，一些为null

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语句:

=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?

这不起作用,如何让文本框显示任何字段?

5 个解决方案

#1

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.

请记住,NULL会胜过数学中的所有内容,因此每次使用任何字段为空时,使用plus运算符都会给出NULL结果。

#2

Easiest solution IMO: Use Nz.

最简单的解决方案IMO:使用Nz。

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

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

虽然你可能想要使用&而不是+。在Access +中表示求和,但是&表示连接。

#3

you were close with your IIf statement.

你和你的IIf声明很接近。

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

#4

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

您可以利用两个连接运算符(+和&)以不同方式处理Null的事实。

? "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:

...但如果您在Null而不是第二个字符串值时不想要两个逗号,请执行以下操作:

? "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.

你不需要函数(IIf,IsNull,Len和/或Nz)来获得你想要的东西。

#5

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

请试试这个:相当超级丑陋的想法......当Chr(13)和Chr(10)在那里它永远不会是空的。

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

#1