Combining Address fields 1

[Chance1234]

Ever find yourself in one of those situations where you ask do you really want to do that ? are you sure ? ok then dont blame me.

I have a table with 8 fields, first one is CustID, the next seven fields are called Address Line 1, Address Line 2, etc etc

what I need to do Is combine all those fields into one, but not all the fields are populated , some records are fully complete others ahve bits and pieces missing from other fields

now they want each Line seperated by a comma,
without writing a million and one If statements anyone know a better way of doing this ?

 

[JoeMiller]

Each line? or each field??

I assume this is going to be for a Mail Merge of some sort. If you do it in word, word will automatically suppress the blanks so you don't have to do anything, just give them it all with the blanks and word will do the rest. Joe Miller
joe.miller@flotech.net

 

[dzshanno]

How about this

Concatenate them all together using '&' and vbNewline and use the nz funtion to avoid the problem of concatenating NULL values



strFullAddress = Nz([CustID],"&quot & Nz([AddressLine1] & "," & vbNewLine,"&quot & Nz([AddressLine2] & "," & vbNewline,"&quot etc

 

[dzshanno]

OOPS I missed a "," out of the first Nz but I think you get the idea

 

[Chance1234]

thanks