|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 16th, 2009, 10:48 AM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
A little code help please
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 
|
|
#2
June 16th, 2009, 03:42 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
A little code help please
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 -- Kind regards Noëlla "pepper" wrote: 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
|
|
#3
June 16th, 2009, 03:49 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
A little code help please
Trim(Nz(fHomeStreet, "") & " " & Nz(fHomeStreetDirection, "")
& " " & Nz(fHomeStreetName, "") & " " & Nz(fHomeApt#, "")) -- _________ Sean Bailey "pepper" wrote: 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
|
|
#4
June 16th, 2009, 05:10 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
A little code help 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#],"")) "pepper" wrote in message ers2... 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
|
|
#5
June 16th, 2009, 05:27 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
A little code help please
fHomeAddress: Trim(([fHomeStreet#] + " ") & ([fHomeStreetDirection] + " ") &
([fHomeStreetName] + " ") & [fHomeApartment#] & " ") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "pepper" wrote in message ers2... 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#],"")) "pepper" wrote in message ers2... 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
|
|
#6
June 16th, 2009, 07:43 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
A little code help please
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 pepper wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
