Function HELP!!

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Lewe22
    New Member
    • Sep 2007
    • 94
    #1

    Function HELP!!

    This is a function i have written to align a series of addresses for printing labels.
    However, for some reason the post code (A8) is not being moved up with the other address lines.

    I can't for the life of me work out why. I need somebody with fresh eyes HELP!!

    Code:
    Function AddressLabels(ADD1, ADD2, ADD3, ADD4, ADD5, TOWN, COUNTRY, PCODE) As String

On Error GoTo AddressLabels_err

Dim A1, A2, A3, A4, A5, A6, A7, A8 As String
Dim i As Integer

A1 = ADD1
A2 = ADD2
A3 = ADD3
A4 = ADD4
A5 = ADD5
A6 = TOWN
A7 = COUNTRY
A8 = PCODE

For i = 1 To 7

If IsNull(A2) Then
    A2 = A3
    A3 = Null
Else
    A2 = A2
    A3 = A3
End If

If IsNull(A3) Then
    A3 = A4
    A4 = Null
Else
    A3 = A3
    A4 = A4
End If

If IsNull(A4) Then
    A4 = A5
    A5 = Null
Else
    A4 = A4
    A5 = A5
End If

If IsNull(A5) Then
    A5 = A6
    A6 = Null
Else
    A5 = A5
    A6 = A6
End If

If IsNull(A6) Then
    A6 = A7
    A7 = ""
Else
    A6 = A6
    A7 = A7
End If

If IsNull(A7) Then
    A7 = A8
    A8 = ""
Else
    A7 = A7
    A8 = A8
End If

Next i

AddressLabels = A1 & Chr(13) & Chr(10) & A2 & Chr(13) & Chr(10) & A3 & Chr(13) & Chr(10) & A4 & Chr(13) & Chr(10) & A5 & Chr(13) & Chr(10) & A6 & Chr(13) & Chr(10) & A7 & Chr(13) & Chr(10) & A8

Exit Function

AddressLabels_err:
    AddressLabels = ""
    Exit Function

End Function
    I am running the function on an access report in a text box which has the following code:

    Code:
    =addresslabels([a1],[a2],[a3],[a4],[a5],[a6],[a7],[a8])
    (a1 - a8 are the address fields supplied from an access query)
    Tags: None
    • MikeTheBike
      Recognized Expert Contributor
      • Jun 2007
      • 640
      #2
      Originally posted by Lewe22
      This is a function i have written to align a series of addresses for printing labels.
      However, for some reason the post code (A8) is not being moved up with the other address lines.

      I can't for the life of me work out why. I need somebody with fresh eyes HELP!!

      Code:
      Function AddressLabels(ADD1, ADD2, ADD3, ADD4, ADD5, TOWN, COUNTRY, PCODE) As String

On Error GoTo AddressLabels_err

Dim A1, A2, A3, A4, A5, A6, A7, A8 As String
Dim i As Integer

A1 = ADD1
A2 = ADD2
A3 = ADD3
A4 = ADD4
A5 = ADD5
A6 = TOWN
A7 = COUNTRY
A8 = PCODE

For i = 1 To 7

If IsNull(A2) Then
    A2 = A3
    A3 = Null
Else
    A2 = A2
    A3 = A3
End If

If IsNull(A3) Then
    A3 = A4
    A4 = Null
Else
    A3 = A3
    A4 = A4
End If

If IsNull(A4) Then
    A4 = A5
    A5 = Null
Else
    A4 = A4
    A5 = A5
End If

If IsNull(A5) Then
    A5 = A6
    A6 = Null
Else
    A5 = A5
    A6 = A6
End If

If IsNull(A6) Then
    A6 = A7
    A7 = ""
Else
    A6 = A6
    A7 = A7
End If

If IsNull(A7) Then
    A7 = A8
    A8 = ""
Else
    A7 = A7
    A8 = A8
End If

Next i

AddressLabels = A1 & Chr(13) & Chr(10) & A2 & Chr(13) & Chr(10) & A3 & Chr(13) & Chr(10) & A4 & Chr(13) & Chr(10) & A5 & Chr(13) & Chr(10) & A6 & Chr(13) & Chr(10) & A7 & Chr(13) & Chr(10) & A8

Exit Function

AddressLabels_err:
    AddressLabels = ""
    Exit Function

End Function
      I am running the function on an access report in a text box which has the following code:

      Code:
      =addresslabels([a1],[a2],[a3],[a4],[a5],[a6],[a7],[a8])
      (a1 - a8 are the address fields supplied from an access query)
      Hi

      Would this be better
      Code:
      Function AddressLabels(ADD1, ADD2, ADD3, ADD4, ADD5, TOWN, COUNTRY, PCODE) As String
 
On Error GoTo AddressLabels_err
 
    AddressLabels = ADD1
 
    If ADD2 & "" <> "" Then AddressLabels = AddressLabels & Chr(13) & Chr(10) & ADD2
    If ADD3 & "" <> "" Then AddressLabels = AddressLabels & Chr(13) & Chr(10) & ADD3
    If ADD4 & "" <> "" Then AddressLabels = AddressLabels & Chr(13) & Chr(10) & ADD4
    If ADD5 & "" <> "" Then AddressLabels = AddressLabels & Chr(13) & Chr(10) & ADD5
    If TOWN & "" <> "" Then AddressLabels = AddressLabels & Chr(13) & Chr(10) & TOWN
    If COUNTRY & "" <> "" Then AddressLabels = AddressLabels & Chr(13) & Chr(10) & COUNTRY
    If PCODE & "" <> "" Then AddressLabels = AddressLabels & Chr(13) & Chr(10) & PCODE
 
Exit Function
 
AddressLabels_err:
    AddressLabels = ""
    Exit Function
 
End Function
      ??

      MTB

        Comment

        • QVeen72
          Recognized Expert Top Contributor
          • Oct 2006
          • 1445
          #3
          Hi,

          Change last part of your code:

          [code=vb]
          If IsNull(A6) Then
          A6 = A7
          A7 = Null
          Else
          A6 = A6
          A7 = A7
          End If

          If IsNull(A7) Then
          A7 = A8
          A8 = Null
          Else
          A7 = A7
          A8 = A8
          End If
          [/code]

          You are checking for Null, but Assigning a Blank String..

          REgards
          Veena

            Comment

            • Lewe22
              New Member
              • Sep 2007
              • 94
              #4
              Cheers QVeen72, knew it would be something easy!
              Last edited by Killer42; Oct 11 '07, 10:48 PM.

                Comment

                Previous template Next
                Working...