Grouping lines together

[Simon]

I use the folloing code in VB that will create a email with the
customer details in

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

If the customer does not have an address line 3 and 4 it apears on the
email like

The Cottage
High Street
London


SW1 9PP

But i would like it to come out like the following with out any gaps
in it

The Cottage
High Street
London
SW1 9PP

 

[Andy Hull]

Hi Simon

Check the line isn't blank before adding it like...

If nz(DLookup("[DeliveryAddress3]", "tblCustomers", "[CustomerNumber]=" &
Me.CustomerNumber),"")<>"" then

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

End If

and do the same for line 4 and any others you want to.

Regards

Andy Hull

 

[John W. Vinson]

I use the folloing code in VB that will create a email with the
customer details in

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

Andy's suggestion is certainly one way to do this - but you're doing this the
hard way! You can instead create a Query on tblCustomers with a calculated
field:

FullAddress: [DeliveryAddressHouseName] & Chr(13) & Chr(10)
& ([DeliveryAddress1] + Chr(13) + Chr(10))
& ([DeliveryAddress2] + Chr(13) + Chr(10))
& ([DeliveryAddress3] + Chr(13) + Chr(10))
& ([DeliveryAddress4] + Chr(13) + Chr(10))
& ([CustomerNumber] + Chr(13) + Chr(10))
& [DeliveryPostCode]

The & and + operators handle nulls differently: for instance if
DeliveryAddress4 is NULL, the entire expression

([DeliveryAddress4] + Chr(13) + Chr(10))

will evaluate to NULL and simply not appear.

You can then DLookUp this FullAddress calculated field, or open the query as a
Recordset and just retrieve it as a recordset field, avoiding the costly
domain function call.



John W. Vinson [MVP]