如何处理Word VBA SQL查询中的空值？

Question

我有一个绑定到 Excel 电子表格中的列的下拉列表。我在下拉列表中选择一个客户，然后使用 Excel 电子表格中的相关数据填充一些地址字段。

代码：

Private Sub cboCompany_Change()
            Dim customerName As String
            customerName = cboCompany.Value
            customerName = Replace(customerName, "'", "''")

            Dim i As Integer
            Dim cn As ADODB.Connection
            Dim rsT As New ADODB.Recordset

            Dim customer As String
            Dim postcode As String
            Dim address1 As String
            Dim suburb As String
            Dim addressType As String
            Dim state As String
            Dim country As String


            Set cn = New ADODB.Connection
            With cn
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "Data Source=C:\Customer.xls;Extended Properties=Excel 8.0;"
            .CursorLocation = adUseClient
            .Open
            End With
            rsT.Open "SELECT Customer, Postcode, [Address 1] AS Address1, [Postal Suburb] AS Suburb, [Address Type] AS AddressType, State, Country FROM Customers WHERE [Address Type] = 'Postal Address' AND Customer = '" & customerName & "'", cn, adOpenStatic

            i = 0

            With rsT
            Do Until .EOF
            customer = rsT.Fields("Customer")
            postcode = rsT.Fields("Postcode")
            address1 = rsT.Fields("Address1")
            suburb = rsT.Fields("Suburb")
            addressType = rsT.Fields("AddressType")
            state = rsT.Fields("State")
            country = rsT.Fields("Country")

            CompanyAddress1.Value = address1
            CompanyAddress2.Value = suburb + " " + state + " " + postcode + " " + country
            CompanyName.Value = customer
            .MoveNext
            i = i + 1
            Loop
            End With
End Sub

但是，如果其中一个字段（例如郊区）为空，则表单会崩溃，那么我该如何处理这个问题？

Answer 1

您可以使用 vba 函数 IsNull。所以像下面这样

If IsNull(rsT.Fields("Customer")) Then

End If

or 

If Not IsNull(rsT.Fields("Customer")) Then

End If

享受吧！