Remove lines from address

[Simon]

I have the following VB code to display customer delivery address
strBody = strBody & "The delivery address for this order is" & vbCrLf
strBody = strBody & DLookup("[DeliveryAddressHouseName]",
"tblCustomers", "[CustomerNumber]=" & Me.CustomerNumber) & vbCrLf
strBody = strBody & DLookup("[DeliveryAddress1]", "tblCustomers",
"[CustomerNumber]=" & Me.CustomerNumber) & vbCrLf
strBody = strBody & DLookup("[DeliveryAddress2]", "tblCustomers",
"[CustomerNumber]=" & Me.CustomerNumber) & vbCrLf
strBody = strBody & DLookup("[DeliveryAddress3]", "tblCustomers",
"[CustomerNumber]=" & Me.CustomerNumber) & vbCrLf
strBody = strBody & DLookup("[DeliveryAddress4]", "tblCustomers",
"[CustomerNumber]=" & Me.CustomerNumber) & vbCrLf
strBody = strBody & DLookup("[DeliveryPostCode]", "tblCustomers",
"[CustomerNumber]=" & Me.CustomerNumber) & vbCrLf & vbCrLf

Only problems is if ther is no DeliveryAddress3 or DeliveryAddress4 it
leaves lines missing

3 The Mall
London


SW4 8LG



How do i get it to change it to
3 The Mall
London
SW4 8LG


Thanks

 

[Ken Sheridan]

Because Null propagates in arithmetical expressions, if you use the +
addition operator rather an the & concatenation operator the carriage
return/line feed will be suppressed if the field is Null. Try this:

Dim strCriteria As String

strCriteria = "[CustomerNumber]=" & Me.CustomerNumber

strBody = strBody & _
"The delivery address for this order is" & vbCrLf
strBody = strBody & _
(DLookup("[DeliveryAddressHouseName]", "tblCustomers", strCriteria) + vbCrLf)
strBody = strBody & _
(DLookup("[DeliveryAddress1]", "tblCustomers", strCriteria) + vbCrLf)
strBody = strBody & _
(DLookup("[DeliveryAddress2]", "tblCustomers", strCriteria) + vbCrLf)
strBody = strBody & _
(DLookup("[DeliveryAddress3]", "tblCustomers", strCriteria) + vbCrLf)
strBody = strBody & _
(DLookup("[DeliveryAddress4]", "tblCustomers", strCriteria) + vbCrLf)
strBody = strBody & _
(DLookup("[DeliveryPostCode]", "tblCustomers", strCriteria) + vbCrLf)
strBody = strBody & vbCrLf

Note that the addition of the carriage return/line feed is parenthesised in
each case apart from the first and last, so if the return value of the
DLookup function is Null the expression within the parentheses will evaluate
to Null and the carriage return/line feed will not be inserted.

Ken Sheridan
Stafford, England

 

[Dirk Goldgar]

I have the following VB code to display customer delivery address
strBody = strBody & "The delivery address for this order is" & vbCrLf
strBody = strBody & DLookup("[DeliveryAddressHouseName]",
"tblCustomers", "[CustomerNumber]=" & Me.CustomerNumber) & vbCrLf
strBody = strBody & DLookup("[DeliveryAddress1]", "tblCustomers",
"[CustomerNumber]=" & Me.CustomerNumber) & vbCrLf
strBody = strBody & DLookup("[DeliveryAddress2]", "tblCustomers",
"[CustomerNumber]=" & Me.CustomerNumber) & vbCrLf
strBody = strBody & DLookup("[DeliveryAddress3]", "tblCustomers",
"[CustomerNumber]=" & Me.CustomerNumber) & vbCrLf
strBody = strBody & DLookup("[DeliveryAddress4]", "tblCustomers",
"[CustomerNumber]=" & Me.CustomerNumber) & vbCrLf
strBody = strBody & DLookup("[DeliveryPostCode]", "tblCustomers",
"[CustomerNumber]=" & Me.CustomerNumber) & vbCrLf & vbCrLf

Only problems is if ther is no DeliveryAddress3 or DeliveryAddress4 it
leaves lines missing

3 The Mall
London


SW4 8LG



How do i get it to change it to
3 The Mall
London
SW4 8LG

For this sort of thing, you can take advantage of the fact that Null +
(anything) = Null. So you can do this:

strBody = strBody & _
(DLookup("[DeliveryAddress3]", "tblCustomers", _
"[CustomerNumber]=" & Me.CustomerNumber) _
+ vbCrLf)

strBody = strBody & _
(DLookup("[DeliveryAddress4]", "tblCustomers", _
"[CustomerNumber]=" & Me.CustomerNumber) _
+ vbCrLf)

If I were you, I'd streamline all those DLookups by using a recordset:

'----- start of revised code -----
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT " & _
"DeliveryAddressHouseName, DeliveryAddress1, " & _
"DeliveryAddress2, DeliveryAddress3, " & _
"DeliveryAddress4, DeliveryPostCode " & _
"FROM tblCustomers " & _
"WHERE CustomerNumber=" & Me.CustomerNumber, _
dbOpenSnapshot)

With rs

If .EOF Then
' What to do if no customer record?
Else
strBody = strBody & _
"The delivery address for this order is" & vbCrLf & _
(!DeliveryAddressHouseName + vbCrLf) & _
(!DeliveryAddress1 + vbCrLf) & _
(!DeliveryAddress2 + vbCrLf) & _
(!DeliveryAddress3 + vbCrLf) & _
(!DeliveryAddress4 + vbCrLf) & _
(!DeliveryPostCode + vbCrLf) & _
vbCrLf
End If

.Close

End With
'----- end of revised code -----

All of this assumes that the "empty" address lines actually are Null, not
just zero-length strings. If they are zero-length strings, you'll need to
actually check the lengths, rather than using the "Null + x = Null" trick.