[AccessD] Insert a new line in an expression?

Joe,
Yes, when I make it:
=Trim(Iif([MailAddr1] Is Not Null, [MailAddr1] & Chr(13) & Chr(10), "") 
& [MailAddr2])
it works.

Tina

Joe O'Connell wrote:
> Tina,
>
> You need both Carriage Return (ASCII 13) and Line Fee (ASCII 10).  Use
> Chr(13) & Chr(10) instead of " ".
>
> Joe
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tina Norris
> Fields
> Sent: Saturday, November 03, 2007 12:10 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Insert a new line in an expression?
>
> It seems that this should be easy, but I'm not getting it.  I have a 
> table of registered voters with their physical addresses and mailing 
> addresses if other than the physical address.  The physical address 
> fields were laid out according to excellent rules, separated into house 
> numbers, directional prefix, roadway name, roadway type, directional 
> suffix, and residential extension.  But, the mailing address fields were
>
> just MailAddr1, MailAddr2, MailAddr3, MailAddr4, and MailAddr5 text 
> fields, with no consistent data entry rules - so there were PO Box 
> numbers in MailAddr1 or MailAddr2, alone or in combination with city 
> name, state name, and sometimes zip code.  In order to use the data for 
> mailing, I spent yesterday cleaning up the mailing address fields so 
> that a "full" street address if any is in MailAddr1, PO Boxes are in 
> MailAddr2, City, State, and Zip are in MailAddr3, MailAddr4, and 
> MailAddr5 respectively.
>
> Now, I am trying to generate a report with these addresses.  In some 
> cases there are both a street address and a PO Box number, such as 2651 
> Gunny's Way NE, PO Box 1534.  But, I don't want a blank line appearing 
> for blank street addresses or PO Box addresses, so I'm trying to put 
> them together in an expression. What I want to do is insert a carriage 
> return character after the street address to put the PO Box on its own 
> print line. 
>
> I have this expression so far:
> =Trim(Iif([MailAddr1] Is Not Null, [MailAddr1]&" ","")&[MailAddr2])
> which strings them out nicely, with a space between them.  Instead of 
> the " " I want to have ASCII 13 or the Access vba equivalent.  I have 
> tried vbCrLf, but Access sticks square brackets around it and demands a 
> value for it as a parameter.  (I've tried a bunch of other stuff, too, 
> but just in case this turns out to be way easy, I am not going to spill 
> the beans about my many failed efforts.)
>
> How do I do this?
>
> TIA
> Tina
>