How to deal with null values in Word VBA SQL query?

Question

Hi,

I have a dropdown that is bound to a column in an excel spreadsheet. I select a customer in the dropdown and then populate some address fields with related data also from the excel spreadsheet.

Code:

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

However, if one of the fields (suburb for example) is empty the form crashes so how can I handle this?

Thanks in advance.

Answer 1

Hi,

You can use the vba function IsNull. So something like the following

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

End If

or 

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

End If

Enjoy!