Trimming address fields

[colkas]

Hi

I have this code that works fine so it brings in address fields

=[SiteName] & " , " & [SiteAddr1] & " , " & [SiteAddr2] & " , " & [SiteCounty] & " , " & [SitePostCode]

Despite many searches on the internet I cannot find the solution to taking out a , when there is no part of the address . So for example if there was no sitecounty it reads

Sitenam, siteaddr1, siteaddr2, ,sitepostcode

As you can see it puts 2 comma's between siteaddr2 and sitepostcode. It is shrinking (I ahve that set) but does not get rid of the comma. Any ideas how I can get rid of the comma if there is not a sitecounty there.

Thanks

 

[Jeanette Cunningham]

Here are a couple of functions that people use to get around that problem.

Function CityStZIPNat(varCity As Variant, varState As Variant, varZIP As Variant, varNation As Variant) As Variant
On Error GoTo Error_Handler

CityStZIPNat = varCity & (", " + varState) & (" " + varZIP) & (IIf(varNation = "US" Or varNation = "CA", "", (" " + varNation)))

Exit_Procedure:
Exit Function
Error_Handler:
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
End Function
Function FullAddr(varAddress1 As Variant, varAddress2 As Variant, varAddress3 As Variant, varAddress4 As Variant, varCity As Variant, varState As Variant, varZIP As Variant, varNation As Variant)
On Error GoTo Error_Handler

Dim strAddr As String

strAddr = IIf(IsNull(varAddress1), "", varAddress1 & Chr(13) & Chr(10))
strAddr = strAddr & IIf(IsNull(varAddress2), "", varAddress2 & Chr(13) & Chr(10))
strAddr = strAddr & IIf(IsNull(varAddress3), "", varAddress3 & Chr(13) & Chr(10))
strAddr = strAddr & IIf(IsNull(varAddress4), "", varAddress4 & Chr(13) & Chr(10))
strAddr = strAddr & CityStZIPNat(varCity, varState, varZIP, varNation)

FullAddr = strAddr

Exit_Procedure:
Exit Function
Error_Handler:
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
End Function

 

[DCrake]

Alot easier and quicker way is to use Replace()

Address = Replace(Address,",,",",")

Thus replacing all intances of ,, with ,

 

[JANR]

Or simply use + instead of & when you concotionate the commas it should not include Null fields:

=[sitename]+" , " & [SiteAddr1]+" , " & [siteaddr2]+" , " & [siteCounty]+" , " & [SitePostcode]

JR

 

[colkas]

Hi

DCrake I like your suggestion but not sure where I out the bit of code

I have my code in the control source
=[SiteName] & " , " & [SiteAddr1] & " , " & [SiteAddr2] & " , " & [SiteCounty] & " , " & [SitePostCode]

So where would I insert you suggestion.

Thanks for the replies

 

[DCrake]

=Trim(Replace([SiteName] & ", " & [SiteAddr1] & ", " & [SiteAddr2] & ", " & [SiteCounty] & ", " & [SitePostCode],", ,", "))

 

[colkas]

Great answers guys, thanks alot, all sorted now.

 

[colkas]

Hi

The voss as decided he does not want the address field in a string but in a block, for example

Name
Address 1
Address 2
County
Post code

So if the county or any other field is empty how do you make it so it goes up one to coover a blank, so if I had

Colins Company
1 high Street

North Yorkshire
BD3 3EE

I would want

Colins Company
1 High Street
North Yorkshire
BD3 3EE

This taking out the blank field. I have tried Can Shrink but this did not work.

Any ideas please

Thanks

 

[missinglinq]

I just love it when two or three of us take the time to help someone out, only to have the 'suits' decide, "Oh, that's not really what I want!" Grrr! Tell the 'voss' to get his head out of his $%## and make up his mind before giving you his requirements!

This takes a completely different approach, so scrap what you have and go with this in the Control Source for the field:

=IIf(Not IsNull([SiteName]), [SiteName] & Chr(13) & Chr(10), "") & IIf(Not IsNull([SiteAddr1]), [SiteAddr1] & Chr(13) & Chr(10), "") & IIf(Not IsNull([SiteAddr2]), [SiteAddr2] & Chr(13) & Chr(10), "") & IIf(Not IsNull([SiteCounty]), [SiteCounty] & Chr(13) & Chr(10), "") & IIf(Not IsNull([SitePostCode]), [SitePostCode] & Chr(13) & Chr(10), "")

Linq ;0)>

 

[stopher]

Or using the + method as proposed by JANR:

=([SiteName] + (Chr(13) & Chr(10))) & ([SiteAddr1] + (Chr(13) & Chr(10))) & ([SiteAddr2] + (Chr(13) & Chr(10))) & ([SiteCounty] + (Chr(13) & Chr(10))) & ([SitePostCode] + (Chr(13) & Chr(10)))

 

[colkas]

Hi

Thanks for the replies, it works well now.

 

[colkas]

Hi

Just another question on this one, what if I needed to have headings next to each one

This is my code

=([sitename]+(Chr(13) & Chr(10))) & ([siteAddr1]+(Chr(13) & Chr(10))) & ([siteAddr2]+(Chr(13) & Chr(10))) & ([siteAddr3]+(Chr(13) & Chr(10))) & ([sitecounty]+(Chr(13) & Chr(10))) & ([sitepostcode]+(Chr(13) & Chr(10)))

So I may want headings like

Compnay Name: Sitename
Addres1: siteAddr1

So is there a way to put headers in the code above?



Thanks

 

[missinglinq]

="Company Name " & ([sitename]+(Chr(13) & Chr(10))) & "Address1 " & ([siteAddr1]+(Chr(13) & Chr(10)))... and so forth.

Linq ;0)>

 

[colkas]

Hi

Thanks for the reply, I think looking at what I am doing I may have asked not quite the correct question. Sorry....

What I am now doing is creating a sub form based on

build1desc and buildingprice1
build2desc and buldingprice2 etc...... all the waa up to 10.

The I get a total price of them then.

Not all prices will be filled in, maybe two on one record and then 4 on another etc,,,,,,,,

SO on the report it looks

Site Building Descrition Price of building

Main £20.00
Hall way £30.00
etc......

If a Site building is not filled in it is just blank or a price not filled in then it leaves 0.00 lines all the way up to 10. It is these blnk lines I need to hide.

So beacuse the descrition comes a field and I ahve it listed as above, any ideas how I can code it please.

Thanks

 

[colkas]

Hi All

I added this bit of code into the control source of a txtbox but I am getting #type1 in the report field instead of the correct text

=[build1desc] & ([building price1]+(Chr(13) & Chr(10))) & [build2desc] & ([buildingprice2]+(Chr(13) & Chr(10)))

the build1desc is the field name

any ideas please