Fixed Address Function

[sike11 via AccessMonster.com]

Hi,
I hope someone can help. I am trying to use the FixedAddress function in one
of my queries as follows:

'NewAddress: FixedAddress([address1],[postcode])'.

However I keep getting an error message stating that the expression is typed
incorrectly or too complex to evaluate. Does any one have any ideas?

I appreciate any help offered.

Mary.

 

[John Spencer]

Since FixedAddress is probably a custom function, it is hard to say what is
wrong.

Some guesses are:
-- Address1 or Postcode is null in some fields and FixedAddress is not set
up to accept nulls
-- FixedAddress function does not exist
-- FixedAddress is getting sent the wrong type of data in one of the
arguments

 

[sike11 via AccessMonster.com]

Hi John,

You are right in that there are some nulls in address1 and postcode. How will
I go about modifying it to accept null values?

Thanks in advance for your help.

Mary.

Since FixedAddress is probably a custom function, it is hard to say what is
wrong.

Some guesses are:
-- Address1 or Postcode is null in some fields and FixedAddress is not set
up to accept nulls
-- FixedAddress function does not exist
-- FixedAddress is getting sent the wrong type of data in one of the
arguments

Hi,
I hope someone can help. I am trying to use the FixedAddress function in

[quoted text clipped - 10 lines]

Mary.

 

[John Spencer]

Well, you modify something by changing it.

Since you haven't posted the function, it is almost impossible to give you
more advice than that. Can you copy the function and post it. You can do
that in this group as a continuation of this thread.

Hi John,

You are right in that there are some nulls in address1 and postcode. How
will
I go about modifying it to accept null values?

Thanks in advance for your help.

Mary.

Since FixedAddress is probably a custom function, it is hard to say what
is
wrong.

Some guesses are:
-- Address1 or Postcode is null in some fields and FixedAddress is not set
up to accept nulls
-- FixedAddress function does not exist
-- FixedAddress is getting sent the wrong type of data in one of the
arguments

Hi,
I hope someone can help. I am trying to use the FixedAddress function in

[quoted text clipped - 10 lines]

Mary.

 

[sike11 via AccessMonster.com]

Hi John,

Thanks for your help. Please find function below:

Public Function FixedAddress(Address As String, PostCode As String)
As String

'vbCrLf Carriage return/line feed
'vbTab - Tab character
'vbCr - Carriage Return

'Declaring arrary
Dim AddressA

AddressA = Split(Address, vbCrLf)

Dim newAddress As String

For Each Line In AddressA
Line = Replace(Line, vbTab, "")
Line = Replace(Line, vbLf, "")
Line = Replace(Line, vbCr, "")
Line = Replace(Line, vbCrLf, "")

If Line <> "" Then
newAddress = newAddress & Line & vbCrLf
End If
Next

FixedAddress = newAddress & PostCode
End Function

Thanks again,

Mary.

Well, you modify something by changing it.

Since you haven't posted the function, it is almost impossible to give you
more advice than that. Can you copy the function and post it. You can do
that in this group as a continuation of this thread.

Hi John,

[quoted text clipped - 22 lines]

 

[Douglas J. Steele]

You can't use For Each Line like that: Access has no idea what Line is.

Try:

Dim intLoop As Integer

For intLoop = LBound(AddressA) To UBound(AddressA)
AddressA(intLoop) = Replace(AddressA(intLoop), vbTab, "")
AddressA(intLoop) = Replace(AddressA(intLoop), vbLf, "")
AddressA(intLoop) = Replace(AddressA(intLoop), vbCr, "")
If Len(AddressA(intLoop))> 0 Then
newAddress = newAddress & AddressA(intLoop) & vbCrLf
End If
Next intLoop

(note that there's no need for the fourth replace, looking for vbCrLf, since
the Split function will already have eliminated all of them)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi John,

Thanks for your help. Please find function below:

Public Function FixedAddress(Address As String, PostCode As
String)
As String

'vbCrLf Carriage return/line feed
'vbTab - Tab character
'vbCr - Carriage Return

'Declaring arrary
Dim AddressA

AddressA = Split(Address, vbCrLf)

Dim newAddress As String

For Each Line In AddressA
Line = Replace(Line, vbTab, "")
Line = Replace(Line, vbLf, "")
Line = Replace(Line, vbCr, "")
Line = Replace(Line, vbCrLf, "")

If Line <> "" Then
newAddress = newAddress & Line & vbCrLf
End If
Next

FixedAddress = newAddress & PostCode
End Function

Thanks again,

Mary.

Well, you modify something by changing it.

Since you haven't posted the function, it is almost impossible to give you
more advice than that. Can you copy the function and post it. You can do
that in this group as a continuation of this thread.

Hi John,

[quoted text clipped - 22 lines]

Mary.

 

[John Spencer]

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,

Thanks for your help. Please find function below:

Public Function FixedAddress(Address As String, PostCode As
String)
As String

'vbCrLf Carriage return/line feed
'vbTab - Tab character
'vbCr - Carriage Return

'Declaring arrary
Dim AddressA

AddressA = Split(Address, vbCrLf)

Dim newAddress As String

For Each Line In AddressA
Line = Replace(Line, vbTab, "")
Line = Replace(Line, vbLf, "")
Line = Replace(Line, vbCr, "")
Line = Replace(Line, vbCrLf, "")

If Line <> "" Then
newAddress = newAddress & Line & vbCrLf
End If
Next

FixedAddress = newAddress & PostCode
End Function

Thanks again,

Mary.

Well, you modify something by changing it.

Since you haven't posted the function, it is almost impossible to give you
more advice than that. Can you copy the function and post it. You can do
that in this group as a continuation of this thread.

Hi John,

[quoted text clipped - 22 lines]

Mary.

 

[sike11 via AccessMonster.com]

Hi,,

Thank you all for your help. I will try both and see how it pans out.

Mary.

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]

 

[sike11 via AccessMonster.com]

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.

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]

 

[John Spencer]

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)

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.

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.