Re: Fixed Address Function

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

Glad it is working. I think the problem with my routine was a mistaken
assumption on my part. If you wanted to strip out all returns and keep them
out, I should have modified the assignment lines to read. I would imagine
that your data was something like 99B <<new line>> Ashburton Road. The 99B
is probably still there, just above the Ashburton Road.

newAddress = newAddress & " " & strLine
'If you don't want the space between the items, then
'you can remove space that is being added
Next iCount

FixedAddress = Trim(newAddress) & " " & PostCode)

"sike11 via AccessMonster.com" <u21678@uwe> wrote in message
news:67f0e058b1a82@xxxxxx
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: 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: 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)