Re: Fixed Address Function

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi,

I tried both functions and both worked well. However, I noticed in John's
function that it stripped out the No. of the house or street so the address
came out like below:

Ashburton Road instead of 99b Ashburton Road.

Thank you all for your help. My query is so much better now.

Regards,

Mary.

John Spencer wrote:
The first line is going to throw an error when address or PostCode is Null.
So that is the first thing to change that line. Then the rest of the code
doesn't look functional to me, so I've rewritten it. This

Public Function FixedAddress(Address, PostCode) As String
'This will always return a string. If Address and Postcode
'are null it will return a zero length string - ""
'vbCrLf Carriage return/line feed
'vbTab - Tab character
'vbCr - Carriage Return
Dim newAddress As String
Dim AddressA As Variant
Dim iCount As Integer
Dim strLine As String

'populate an array with the lines
AddressA = Split(Address, vbCrLf)

'Combine the results
For iCount = LBound(AddressA) To UBound(AddressA)
'Strip out tab, linefeeds, and carriage returns
' You may want to modify this to put in a space or spaces in place of
the
' character you are replacing. Otherwise, you could end up with
' data run together as in 1234(Tab)X Street would return 1234X Street

strLine = Replace(AddressA(iCount), vbTab, "")
strLine = Replace(strLine, vbLf, "")
strLine = Replace(strLine, vbCr, "")
strLine=Replace(strLine," "," ") 'Two Spaces become one space

'combine the lines and add a carriagereturn and linefeed in
newAddress = newAddress & vbCrLf & strLine
Next iCount

FixedAddress = Trim(Mid(newAddress, 3) & " " & PostCode)

End Function

Hi John,

[quoted text clipped - 44 lines]

Mary.

--
Message posted via http://www.accessmonster.com

.

Relevant Pages

  • Re: Fixed Address Function
    ... Public Function FixedAddressAs String ... 'vbCrLf Carriage return/line feed ... Dim newAddress As String ... Dim strLine As String ...
    (microsoft.public.access.queries)
  • Re: Build an array from a text file
    ... Dim strLine As String ... Dim strMaterial As String ... ' Test dictionary by checking on the value for material Steel ...
    (microsoft.public.word.vba.general)
  • Re: Fixed Address Function
    ... Public Function FixedAddressAs String ... 'vbCrLf Carriage return/line feed ... Dim newAddress As String ... Dim strLine As String ...
    (microsoft.public.access.queries)
  • Re: Text file I/O in VBA
    ... which the header or other lines will not have. ... Dim f1 As Integer ... Dim fOutput As String ... Input #1, strLIne ...
    (microsoft.public.access.modulesdaovba)
  • Re: Appending text files
    ... "carriage return" in your post) only if there isn't one there already. ... Sub JoinFiles(sFileNew As String, ParamArray sFiles() As Variant) ... Dim FileIn As Long ... Dim FileOut As Long ...
    (microsoft.public.vb.general.discussion)