How can I test if multiple fields are null? (1 Viewer)

[robert693]

I have a module that concatenates different fields from a table for mailing labels. The fields are First Name, Last Name, Address and so on. The problem is that if a field is null an error message appears that says "Invalid use of null". I tried several ways to test if the field is null first but it hasn't worked. I will put the code in here and if there is a way to do this could somebody help? I would appreciate it!

Function Mashit()
Dim CRLF As Variant, TheDB As Database, Tb1 As Table
Dim N1 As String, N2 As String, N3 As String
Set TheDB = CurrentDb()
Set Tb1 = TheDB.OpenTable("LEADS")
Tb1.Index = "Last Name"
Tb1.MoveFirst
CRLF = Chr(13) & Chr(10)
Do Until Tb1.EOF
'If IsNull(Tb1("Mashed")) Then
If Not IsNull(Tb1("First Name")) Then
If Not IsNull(Tb1("Company")) Then
If Not IsNull(Tb1("Address 1")) Then
If Not IsNull(Tb1("Address 2")) Then
N1 = Tb1("First Name") & " " & Tb1("Middle Initial") & " " & Tb1("Last Name")
N2 = Tb1("Company") & CRLF & Tb1("Address 1")
N3 = Tb1("Address 2") & CRLF & Tb1("City") & ", " & Tb1("State") & " " & Tb1("Zip") & " " & Tb1("Country")
Tb1.Edit
Tb1("Mashed") = N1 & CRLF & N2 & CRLF & N3
Tb1.Update
Else 'address2
N1 = Tb1("First Name") & " " & Tb1("Middle Initial") & " " & Tb1("Last Name")
N2 = Tb1("Company") & CRLF & Tb1("Address 1")
N3 = Tb1("City") & ", " & Tb1("State") & " " & Tb1("Zip") & " " & Tb1("Country")
Tb1.Edit
Tb1("Mashed") = N1 & CRLF & N2 & CRLF & N3
Tb1.Update
End If
Else
If Not IsNull(Tb1("Address 2")) Then
N1 = Tb1("First Name") & " " & Tb1("Middle Initial") & " " & Tb1("Last Name")
N2 = Tb1("Company") & CRLF & Tb1("Address 2")
N3 = Tb1("City") & ", " & Tb1("State") & " " & Tb1("Zip") & " " & Tb1("Country")
Tb1.Edit
Tb1("Mashed") = N1 & CRLF & N2 & CRLF & N3
Tb1.Update
Else
N1 = Tb1("First Name") & " " & Tb1("Middle Initial") & " " & Tb1("Last Name")
N2 = Tb1("Company")
N3 = Tb1("City") & ", " & Tb1("State") & " " & Tb1("Zip") & " " & Tb1("Country")
Tb1.Edit
Tb1("Mashed") = N1 & CRLF & N2 & CRLF & N3
Tb1.Update
End If
End If
Else

If Not IsNull(Tb1("Address 2")) Then
N1 = Tb1("First Name") & " " & Tb1("Middle Initial") & " " & Tb1("Last Name")
N2 = Tb1("Address 1") & CRLF & Tb1("Address 2")
N3 = Tb1("City") & ", " & Tb1("State") & " " & Tb1("Zip") & " " & Tb1("Country")
Tb1.Edit
Tb1("Mashed") = N1 & CRLF & N2 & CRLF & N3
Tb1.Update
Else
N1 = Tb1("First Name") & " " & Tb1("Middle Initial") & " " & Tb1("Last Name")
N2 = Tb1("Address 1")
N3 = Tb1("City") & ", " & Tb1("State") & " " & Tb1("Zip") & " " & Tb1("Country")
Tb1.Edit
Tb1("Mashed") = N1 & CRLF & N2 & CRLF & N3
Tb1.Update
End If
End If
Else
If Not IsNull(Tb1("Address 2")) Then
N2 = Tb1("Company") & CRLF & Tb1("Address 1")
N3 = Tb1("Address 2") & CRLF & Tb1("City") & ", " & Tb1("State") & " " & Tb1("Zip") & " " & Tb1("Country")
Tb1.Edit
Tb1("Mashed") = N2 & CRLF & N3
Tb1.Update
Else
N2 = Tb1("Company") & CRLF & Tb1("Address 1")
N3 = Tb1("City") & ", " & Tb1("State") & " " & Tb1("Zip") & " " & Tb1("Country")
Tb1.Edit
Tb1("Mashed") = N2 & CRLF & N3
Tb1.Update
End If

End If
'End If


Tb1.MoveNext
Loop
Tb1.Close

End Function

 

[robert693]

I forgot to add that multiple fields can be null at the same time.. such as a Name but no Company, or Name, Company but no Address.

 

[Travis]

Try This:

Function Mashit2()
Dim TheDB As Database, Tb1 As DAO.Recordset
Dim N1 As Variant, N2 As Variant, N3 As Variant, N4 As Variant, N5 As Variant
Set TheDB = CurrentDb()
Set Tb1 = TheDB.OpenRecordset("LEADS")
Tb1.Index = "Last Name"
Tb1.MoveFirst
Do While Not Tb1.EOF
N1 = Tb1("First Name") & (" " + Tb1("Middle Initial")) & (" " + Tb1("Last Name"))
N2 = Tb1("Company")
N3 = Tb1("Address 1")
N4 = Tb1("Address 2")
N5 = Tb1("City") & (", " + Tb1("State")) & (" " + Tb1("Zip")) & (" " + Tb1("Country"))

Tb1.Edit
Tb1("Mashed") = N1 & IIf(IsNull(N1), "", vbCrLf) & N2 & IIf(IsNull(N2), "", vbCrLf) & N3 & IIf(IsNull(N3), "", vbCrLf) & N4 & IIf(IsNull(N4), "", vbCrLf) & N5
Tb1.Update
Tb1.MoveNext
Loop

End Function