Trim a whole addres

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2000.
I know I have seen this but can not find where. How do you trim the whole
address (name, attn:, add1, add2) so that it does not leave empty lines if
there is no information in one of the fields. I can do the trim for "city,
st and zip" but that is all on one line. Help Please!!
 
Aurora said:
I am using Access 2000.
I know I have seen this but can not find where. How do you trim the
whole address (name, attn:, add1, add2) so that it does not leave
empty lines if there is no information in one of the fields. I can
do the trim for "city, st and zip" but that is all on one line. Help
Please!!
Click to expand...

The trick is doing concatenation with "+" because "+" does not propogate a
Null like "&" does.

(assuming [name] will never be null here)

=[name] & (Chr(13) + Chr(10) + [attn]) & (Chr(13) + Chr(10) + [add1]) &
(Chr(13) + Chr(10) + [add2])
 
Rick:
From your reply - What is "Char(13) + Char(14)" represent?

Aurora

Rick Brandt said:
Aurora said:
I am using Access 2000.
I know I have seen this but can not find where. How do you trim the
whole address (name, attn:, add1, add2) so that it does not leave
empty lines if there is no information in one of the fields. I can
do the trim for "city, st and zip" but that is all on one line. Help
Please!!
Click to expand...

The trick is doing concatenation with "+" because "+" does not propogate a
Null like "&" does.

(assuming [name] will never be null here)

=[name] & (Chr(13) + Chr(10) + [attn]) & (Chr(13) + Chr(10) + [add1]) &
(Chr(13) + Chr(10) + [add2])
Click to expand...
 
Aurora said:
Rick:
From your reply - What is "Char(13) + Char(14)" represent?
Click to expand...

I assumed you wanted the output on separate lines. Those are the
CarriageReturn and LineFeed characters that would be required to get a
multi-line output from the expression.

And the seocnd one is actually Chr(10), not Chr(14).
 
Aurora: Just to build on what Rick said (which is absolutely what you need),
I have created a very simple Function that I now put in a Module in all my
applications to ease this sort of approach:

Public Function NL()
NL = vbCrLf
End Function

Where NL means New Line to me.

Then when I set up an address, I do it like this:

=Trim([Line1]) + NL() & Trim([Line2]) + NL() & Trim([Line3]) + NL() ... and
so forth.

This reduces my keystrokes - and my old brain as well (I always had to go
look up the Char numbers to make sure I got them right and in the right
order!).

HTH Joe
 
Thank you both for your help. You don't know how much I rely on and
appreciate the help I get from this newsgroup.

Rick -- How do I create the Function and put it in a Module like you did?
Aurora
 
Aurora: I assume you meant "Joe" referring to the function. If that is the
case, just open the MDB, go to the Database window (F11 should get you
there), go to the Modules tab and click on New. When the module window comes
up, just copy (or type in) the three line function as I have shown:

Public Function NL()
NL = vbCrLf
End Function

Save the module as any name (say, New Line Function). Then you can use the
function as I indicated whenever you need it.

HTH Joe
 
Joe - This is what I put in my report, but I am getting an error and can't
figure out what I have typed wrong. Can you see what you think?

=Trim([coname])+ NL() & Trim(attn:]) + NL() & Trim([add1]) + NL() &
trim([add2]) + NL() & Trim([City])&", " & [st]&" " & [zip]
Aurora
 
Aurora: Check the brackets around attn: - looks like you left out the left
square bracket. Should be:

Trim([attn:])

Everything else seems okay and ready for another test.

I also, as a matter of habit, do not use special characters in field names
(except sometimes "_" - the "underline"). There are too many cases where
this can be a problem (although I don't think it is this specific case). You
could name the field simply "Attn" and then put anything you want in a form
or report label.

Joe

Aurora said:
Joe - This is what I put in my report, but I am getting an error and can't
figure out what I have typed wrong. Can you see what you think?

=Trim([coname])+ NL() & Trim(attn:]) + NL() & Trim([add1]) + NL() &
trim([add2]) + NL() & Trim([City])&", " & [st]&" " & [zip]
Aurora


Joe D said:
Aurora: I assume you meant "Joe" referring to the function. If that is the
case, just open the MDB, go to the Database window (F11 should get you
there), go to the Modules tab and click on New. When the module window comes
up, just copy (or type in) the three line function as I have shown:

Public Function NL()
NL = vbCrLf
End Function

Save the module as any name (say, New Line Function). Then you can use the
function as I indicated whenever you need it.

HTH Joe
Click to expand...
Click to expand...
 
Back
Top