Removing blanks fields from query

[Moss100]

Hello All,

I use the code below to format addresses.

It works generally ok, but sometimes I think an empty field is not picked up as Null and thus carriage return is put in when not required.

Is there a way to modify the code to test for empty as well as null fields?

Many thanks for any help. If someone could modify part of the code this would help greatly as my coding is a little poor.




Address_Multi_line: IIf(Not IsNull([address no]),"" & [address no] & " ","") & IIf(Not IsNull([Address 1]),"" & [Address 1] & "","") & Chr(13) & Chr(10) & IIf(Not IsNull([Address 2]),"" & [Address 2] & "","") & Chr(13) & Chr(10) & IIf(Not IsNull([Address 3]),"" & [Address 3] & "","") & Chr(13) & Chr(10) & IIf(Not IsNull([Address 4]),"" & [Address 4] & "","") & Chr(13) & Chr(10) & IIf(Not IsNull([Address postcode]),"" & [Address postcode] & "","")

 

[Moss100]

The following seems to work - it it ok - or should I be doing something better?

Thanks Mark

Address: IIf([address no]<>"","" & [address no] & " ","") & IIf([address 1]<>"","" & [address 1] & "","") & IIf([address 2]<>"","" & ", " & [address 2] & "","") & IIf([address 3]<>"","" & ", " & [address 3] & "","") & IIf([address 4]<>"","" & ", " & [address 4] & "","") & IIf([address postcode]<>"","" & ", " & [address postcode] & "","")

 

[PHV]

Replace this:
Not IsNull([address XX])
with this:
Trim([address XX] & "")<>""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Moss100]

Thank you very much