A little code help please

[pepper]

Hi, I'm fairly new to access and I'm just trying to address an envelope.
I'm trying to do a query where if the 'Street Number' or 'Street Direction'
are null, there won't be a space in front of the 'Street Name'. This is
because I put the addresses with PO Boxes in the 'Street Name' column.

This is what I have so far...

fHomeAddress: IIf(IsNull([fHomeStreet#]),"",[fHomeStreet#]) &
IIf(IsNull([fHomeStreetDirection]),""," " & [fHomeStreetDirection]) & " " &
[fHomeStreetName] & " " & [fHomeApartment#]

This phrase leaves a space in front of the street name when the street name
is a PO Box as I'm sure you all know. Can someone help me eliminate the
space in those cases.

Thanks very much,

Pepper

 

[NG]

Hi,

* first you can use the NZ function to simplify the expression:
NZ([fHomeStreet#],"") equals IIf(IsNull([fHomeStreet#]),"",[fHomeStreet#])

* to eliminate extra spaces use the Trim function

 

[Beetle]

Trim(Nz(fHomeStreet, "") & " " & Nz(fHomeStreetDirection, "")
& " " & Nz(fHomeStreetName, "") & " " & Nz(fHomeApt#, ""))

 

[pepper]

Thanks to you both but I'm still getting two spaces if both fHomeStreet# and
fHomeStreetDirection are blank. I think it's the two spaces in between the
two fields that are accumulating. Any additionals suggestions. Thanks very
much.


fHomeAddress: Trim(Nz([fHomeStreet#],"") & " " &
Nz([fHomeStreetDirection],"") & " " & Nz

([fHomeStr`eetName],"") & " " & Nz([fHomeApartment#],""))

 

[Douglas J. Steele]

fHomeAddress: Trim(([fHomeStreet#] + " ") & ([fHomeStreetDirection] + " ") &
([fHomeStreetName] + " ") & [fHomeApartment#] & " ")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks to you both but I'm still getting two spaces if both fHomeStreet#
and fHomeStreetDirection are blank. I think it's the two spaces in
between the two fields that are accumulating. Any additionals
suggestions. Thanks very much.


fHomeAddress: Trim(Nz([fHomeStreet#],"") & " " &
Nz([fHomeStreetDirection],"") & " " & Nz

([fHomeStr`eetName],"") & " " & Nz([fHomeApartment#],""))

Hi, I'm fairly new to access and I'm just trying to address an envelope.
I'm trying to do a query where if the 'Street Number' or 'Street
Direction' are null, there won't be a space in front of the 'Street
Name'. This is because I put the addresses with PO Boxes in the 'Street
Name' column.

This is what I have so far...

fHomeAddress: IIf(IsNull([fHomeStreet#]),"",[fHomeStreet#]) &
IIf(IsNull([fHomeStreetDirection]),""," " & [fHomeStreetDirection]) & " "
& [fHomeStreetName] & " " & [fHomeApartment#]

This phrase leaves a space in front of the street name when the street
name is a PO Box as I'm sure you all know. Can someone help me eliminate
the space in those cases.

Thanks very much,

Pepper

 

[John Spencer MVP]

You could do something like the following.

FullName: ([Prefix] + " ") & ([Title] + " ")
& ([FirstName] + " ") & [LastName]

FullAddress: (Address1 + chr(13) + chr(10)) &
(Address2 + chr(13) + chr(10)) &
(City + ", ") & (State + " ") & Zip

This technique relies on the different ways that Access handles concatenating
(combining strings). If a string is Null and you add (plus sign) to another
string then you get a null. If a string is null and you combine it (& sign)
to another string you get the other string.

So if the Title is null and the prefix is null you don't end up with leading
spaces.
Or if Address 2 is null you don't end up with a blank line.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County