Grouping lines together

  • Thread starter Thread starter Simon
  • Start date Start date
S

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
 
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
 
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
Click to expand...

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]
 
Back
Top