如何在 MS Access 中过滤掉数组的空元素

Question

我正在从其他几个字段中生成标题，并且想要“正确”的方法：

Me.Title.Value = Join(Array([Conference], [Speaker], partstr), " - ")

除了 [conference]、[speaker] 或 partstr 中的任何一个可能为空，并且我不想要额外的“-”' s。 是否有任何功能可以使这项工作变得简单？

Answer 1

不 - 你必须检查每一项，然后在最后进行清理

Dim Temp As String

If Not IsNull([Conference]) Then
  Temp = Temp & [Conference] & " - "
End If

If Not IsNull([Speaker]) Then
  Temp = Temp & [Speaker] & " - "
End If

If Not IsNull(partstr) Then
  Temp = Temp & partstr & " - "
End If

If Temp > "" then
  Me.Title.Value = Left(Temp, Len(Temp) - 3)
Else
  Me.Title.Value = Null
End If

使用通用函数进行修改：

Public Function JoinEx(ByVal pArray As Variant, ByVal pDelimiter As String) As String

  Dim sTemp As String
  Dim iCtr As Integer

  For iCtr = 0 To UBound(pArray)
    If Not IsNull(pArray(iCtr)) Then
      sTemp = sTemp & pArray(iCtr) & pDelimiter
    End If
  Next

  If sTemp > "" Then
   JoinEx = Left(sTemp, Len(sTemp) - Len(pDelimiter))
  End If

End Function

调用示例：

 JoinEx(Array("one","two","three"), " - ")  'Returns "One - Two - Three"
 JoinEx(Array(null,"two","three"), " - ")  'Returns "Two - Three"

Answer 2

完全重写，这次经过了正确的测试：

 IIf(IsNull(Partstr), IIf(IsNull(Conference), Speaker, Conference & " - " + Speaker), Conference + " - " & Speaker + " - " & Partstr)