Dealing with multiple Empty fields in a label

[rudger]

I'm not sure which forum I should post this in so Mods if you feel that it is in the wrong forum I apologise in advance

on my form I have a Label that I want to contain A customers contact details these fields are stored in a table using the following format

SiteCompanyName
SiteContactTitle
SiteContactFirstName
SiteContactSurname
SiteAddressLine1
SiteAddressLine2
SiteAddressLine3
SiteAddressLine4
SiteAddressCounty
SiteAddressPostCode
SitePhoneNumber
SiteMobileNumber

not all customers have all fields completed (some may only have 1 or 2) I am currently using Dlookup to gain each entry as a variant then checking if the field isNull before adding a label to the form with all the details.

This causes empty lines in the label on the form and I would like be able to ignore the empty fields my code looks like below

Dim cn As Variant
Dim fn As Variant
Dim a1 As Variant
Dim a2 As Variant
Dim a3 As Variant
Dim a4 As Variant
Dim ac As Variant
Dim ap As Variant

fn = DLookup("[siteformal]", "CustomerDetailsQ", "customerID=" & CustomerID)
cn = DLookup("[SiteCompanyname]", "CustomerDetailsQ", "customerID=" & CustomerID)
a1 = DLookup("[SiteAddressLine1]", "CustomerDetailsQ", "customerID=" & CustomerID)
a2 = DLookup("[SiteAddressLine2]", "CustomerDetailsQ", "customerID=" & CustomerID)
a3 = DLookup("[SiteAddressLine3]", "CustomerDetailsQ", "customerID=" & CustomerID)
a4 = DLookup("[SiteAddressLine4]", "CustomerDetailsQ", "customerID=" & CustomerID)
ac = DLookup("[SiteAddressCounty]", "CustomerDetailsQ", "customerID=" & CustomerID)
ap = DLookup("[SiteAddressPostCode]", "CustomerDetailsQ", "customerID=" & CustomerID)

If IsNull(fn) Then fn = " "
If IsNull(cn) Then cn = " "
If IsNull(a1) Then a1 = " "
If IsNull(a2) Then a2 = " "
If IsNull(a3) Then a3 = " "
If IsNull(a4) Then a4 = " "
If IsNull(ac) Then ac = " "
If IsNull(ap) Then ap = " "


Me.lblCustomerDetails.Caption = fn + vbCrLf + cn + vbCrLf + a1 + vbCrLf + a2 + vbCrLf + a3 + vbCrLf + a4 + vbCrLf + ac + vbCrLf + ap

are there any suggestions on how I can simplify my code and avoid the unnecessary empty lines

Thanks in Advance

Rudger

 

[plog]

Wy don't you use a bound form for this data?

From a coding perspective you're function could be cleaned up a lot by using an array.
Load the field names into an array, make your lookup code reference that array and loop through it for every field you want to retrieve and test.

' psuedo code below

CaptionText = ""
MyArray = array("siteformal", "SiteCompanyname", "SiteAddressLine1")

for each item in MyArray
  temp = Dlookup(item)
  if Isnull(temp) then temp = ""
  CaptionText & = temp
end for each

Me.Label = CaptionText

Looking closer at the code, if an item is null, don't just make it a blank- don't add it at all to the caption text variable

 

[Gasman]

Test for NULL and then only add the string if not null?

Also I'd probably get the whole record in one pass with a recordset instead of all those DLookups?

 

[Mark_]

shouldn't

temp = Dlookup(item)
  if Isnull(temp) then temp = ""

be

temp = nz(Dlookup(item),"")

 

[Mark_]

OP,

For your particular case, I would replace

Me.lblCustomerDetails.Caption = fn + vbCrLf + cn + vbCrLf + a1 + vbCrLf + a2 + vbCrLf + a3 + vbCrLf + a4 + vbCrLf + ac + vbCrLf + ap

with

Dim asCaption as String
if fn <> "" then asCaption = asCaption & fn & vbCrLf
if cn <> "" then asCaption = asCaption & cn & vbCrLf
if a1 <> "" then asCaption = asCaption & a1 & vbCrLf
if a2 <> "" then asCaption = asCaption & a2 & vbCrLf
if a3 <> "" then asCaption = asCaption & a3 & vbCrLf
if a4 <> "" then asCaption = asCaption & a4 & vbCrLf
if ac <> "" then asCaption = asCaption & ac & vbCrLf
if ap <> "" then asCaption = asCaption & ap
Me.lblCustomerDetails.Caption = asCaption

You can then remove the last two characters if you are really concerned about the trailing vbCrlf. Alternately create a little function to prepend the vbCrLf to the string you are going to add and not worry about it.

 

[arnelgp]

Use

(Var1 + vbcrlf) & (var2 + vbcrlf) + ...