concatenation and trim blanks? 1

[kimmole]

i,m trying to get a report/query to concatenate fields, place carriage returns after each field and print with no blank lines..... it drives me cwazzzzzzy!!
any ideas?

 

[KenReay]

Hi

Have not tried code below, because my copy of Access has just died on me, but I think it will work:

X:=Trim(Field1) & IIF(Len(Trim(Field1))=0,"",vbcrlf) & Trim(Field2) & IIF(Len(TRim(Field2))=0,"",vbcrlf)...etc
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[kimmole]

tanks ken looks good to me.... i'll go and try it!!

thanks again

 

[randysmid]

Ken,
As always, a great tip!!!!! Another star for your helpful posts.
Randy HTH,
Randy Smith
California Teachers Association

 

[kimmole]

definatly does the concatenation now all i need isa the char for a carriage return and a space between concatenated filds...
so mrkimmole6foundrysquare

comes out...

mr kim mole
6 foundry square

 

[KenReay]

Hi

I included the cr and lf, it is vb constant vbcrlf, or you can use chr(13) & chr(10) Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[kimmole]

thanks again ken....
i'll use the chr bits as i havent a clue what you're on about asregards the vb bit....
....sorry i still use macros
but i'll hget there
ta again kimbo))))

 

[kimmole]

er ken..... i get a square symbol when i use chr (13) or chr(10)
i've seen the square symbol come in in imported asci text before so mi guess i'm using the wrong symbol for a carriage return...
so far i,ve got....
=Trim([Home Address]) & IIf(Len(Trim([Home Address]))=0,"",Chr(13)) & Trim([Address]) & IIf(Len(Trim([Address]))=0,"",Chr(13)) & Trim([City]) & IIf(Len(Trim([City]))=0,"",Chr(13)) & Trim([State]) & IIf(Len(Trim([State]))=0,"",Chr(13)) & Trim([Zip/Postal Code]) & IIf(Len(Trim([Country]))=0,"",Chr(13)) & Trim([Zip/Postal Code]) & IIf(Len(Trim([Country]))=0,"&quot

 

[kimmole]

i'v now changed it using "is null rather than =0
so...
[x]=Trim([Home Address]) & IIf(Len(Trim([Home Address])) Is Null,"",Chr(13)) & Trim([Address]) & IIf(Len(Trim([Address])) Is Null,"",Chr(13)) & Trim([City]) & IIf(Len(Trim([City])) Is Null,"",Chr(13)) & Trim([State]) & IIf(Len(Trim([State])) Is Null,"",Chr(13)) & Trim([Zip/Postal Code]) & IIf(Len(Trim([Country])) Is Null,"",Chr(13)) & Trim([Zip/Postal Code]) & [Country]