[AccessD] Insert a new line in an expression?

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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com