Using an Address Block Module for a report

[mcgeester]

I am generating a report for a directory with addresses, phone numbers, addresses, children's names, etc. I am calling up an Address Block Module with 6 variables for the phone numbers and email addresses, but I have up to 8 variables. When I try to edit the Address Block Module to include 2 more variables, my report returns an error. Here is how the Module is written:



Function AddressBlock$(AName, Addr1, Addr2, City, State, Zip)

Dim A1$, A2$, A3$, A4$, A5$, A6$, CR$

CR$ = Chr(13) & Chr(10) 'Carriage return and line feed.

A1$ = IIf(ISB(AName), "", AName & CR$)
A2$ = IIf(ISB(Addr1), "", Addr1 & CR$)
A3$ = IIf(ISB(Addr2), "", Addr2 & CR$)
A4$ = IIf(ISB(City), "", City & CR$)
A5$ = IIf(ISB(State), "", State & CR$)
A6$ = "" & Zip

AddressBlock = A1$ & A2$ & A3$ & A4$ & A5$ & A6$ 'Concatenate the strings.
End Function

Function ISB(V) As Integer
If IsNull(V) Or V = "" Then ISB = True Else ISB = False
End Function

I tried to add State2 and State3 keeping all the conventions, but my report would then return an error.

I did not write this code and am just a barely competent Access user, so any help you can give would be appreciated.

Thanks.

[pbaldy]

It would help if you posted the failing code. How does an address fall into more than one state?

[mcgeester]

So this Address Block works with the phone numbers and emails in the db. In my report there is a text box with the control source as:

=AddressBlock([Phone2],(IIf(IsNull([No List Phone3]),(IIf(IsNull([Phone3]),Null,[Phone3])),Null)),(IIf(IsNull([NO List fax]),(IIf(IsNull([fax]),Null,"Fax: " & [fax])),Null)),(IIf(IsNull([No List Phone4]),(IIf(IsNull([Phone4]),Null,[Phone4])),Null)),(IIf(IsNull([NO List e-mail]),(IIf(IsNull([e-mail]),Null,[e-mail])),Null)),(IIf(IsNull([NO List e-mail2]),(IIf(IsNull([e-mail2]),Null,[e-mail2])),Null)))

The Address Block Module was written as I first posted and when I run the report the results in this text box give me up to 4 phone numbers and 2 email addresses in a column with no empty lines if one of the fields is blank. I don't know how it works, but I'm guessing the Name, Address1, Address2, City, State and Zip of the Address Block are just 6 different variables and it doesn't matter what the field names are.

So this is how I changed it, hoping to just add 2 more variables because I have 2 more email addresses that I need to include. Instead of State2 and State3, I probably could have used Address3 and Address4, but I just went to the end of the list and started to add and I didn't want to mess with the Zip statement because it was different than the ones above it. I was just hoping to slide two more variables in there:

Function AddressBlock$(AName, Addr1, Addr2, City, State, State2, State3, Zip)

Dim A1$, A2$, A3$, A4$, A5$, A6$, A7$, A8$, CR$

CR$ = Chr(13) & Chr(10) 'Carriage return and line feed.

A1$ = IIf(ISB(AName), "", AName & CR$)
A2$ = IIf(ISB(Addr1), "", Addr1 & CR$)
A3$ = IIf(ISB(Addr2), "", Addr2 & CR$)
A4$ = IIf(ISB(City), "", City & CR$)
A5$ = IIf(ISB(State), "", State & CR$)
A6$ = IIf(ISB(State2,) "", State2 & CR$)
A7$ = IIf(ISB(State3,) "", State3 & CR$)
A8$ = "" & Zip

AddressBlock = A1$ & A2$ & A3$ & A4$ & A5$ & A6$ & A7$ & A8$ 'Concatenate the strings.
End Function

Function ISB(V) As Integer
If IsNull(V) Or V = "" Then ISB = True Else ISB = False
End Function

I'm hoping this makes some sense to someone!!!

[mcgeester]

Oh yeah - and I just want to say that when I ran the report with the module written for 8 variables, I got an error, but when I run it as originally written with 6 variables, it works like a charm (except I don't get those 3rd and 4th email addresses in my list)

[ssanfu]

You have the comma's in two lines in the wrong place. They should be after the closing parenthesis.

A6$ = IIf(ISB(State2,) "", State2 & CR$)
A7$ = IIf(ISB(State3,) "", State3 & CR$)

I re-wrote your code - using the $ sign to declare variables as string has been depreciated.

Function AddressBlock$(AName, Addr1, Addr2, City, State, email1, email2, Zip)

    Dim sA1 As String, sA2 As String, sA3 As String, sA4 As String
    Dim sA5 As String, sA6 As String, sA7 As String, sA8 As String
    Dim sCR As String

    sCR = Chr(13) & Chr(10)    'Carriage return and line feed.

    sA1 = IIf(ISB(AName), "", AName & sCR)
    sA2 = IIf(ISB(Addr1), "", Addr1 & sCR)
    sA3 = IIf(ISB(Addr2), "", Addr2 & sCR)
    sA4 = IIf(ISB(City), "", City & sCR)
    sA5 = IIf(ISB(State), "", State & sCR)
    sA6 = IIf(ISB(email1), "", email1 & sCR)
    sA7 = IIf(ISB(email2), "", email2 & sCR)
    sA8 = "" & Zip

    'Concatenate the strings.
    AddressBlock = sA1 & sA2 & sA3 & sA4 & sA5 & sA6 & sA7 & sA8

End Function

Function ISB(V) As Integer
    ISB = False  ' set default return value
    
    If IsNull(V) Or V = "" Then
        ISB = True
    End If
    
End Function

The code could also be written to eliminate the function ISB()

Function AddressBlock$(AName, Addr1, Addr2, City, State, email1, email2, Zip)

    Dim sA1 As String, sA2 As String, sA3 As String, sA4 As String
    Dim sA5 As String, sA6 As String, sA7 As String, sA8 As String
    Dim sCR As String

    sCR = Chr(13) & Chr(10)    'Carriage return and line feed.
   ' could use the following line instead
   '   sCR = vbNewLine

    sA1 = IIf(Len(Trim(AName)) = 0, "", AName & sCR)
    sA2 = IIf(Len(Trim(Addr1)) = 0, "", Addr1 & sCR)
    sA3 = IIf(Len(Trim(Addr2)) = 0, "", Addr2 & sCR)
    sA4 = IIf(Len(Trim(City)) = 0, "", City & sCR)
    sA5 = IIf(Len(Trim(State)) = 0, "", State & sCR)
    sA6 = IIf(Len(Trim(email1)) = 0, "", email1 & sCR)
    sA7 = IIf(Len(Trim(email2)) = 0, "", email2 & sCR)
    sA8 = "" & Zip

    'Concatenate the strings.
    AddressBlock = sA1 & sA2 & sA3 & sA4 & sA5 & sA6 & sA7 & sA8

End Function

[mcgeester]

well, I had high hopes for this working, but it didn't. I copied the first set of code into the module, saved it and then ran my report. Where the Address Block should print out, I get #Name?. Then I tried the second set of code and the same thing happened. I don't know if there's another control somewhere that I'm not seeing that prevents this new Address Block module from working or what's happening. Logic says this should work!

[orange]

Can you post a copy of your database with enough records to show the issue? It's hard to debug with no code/database.

[mcgeester]

OK - here it is with a sampling of data

[pbaldy]

I'm not getting any errors or #Name anywhere. By the way, what's the purpose of this?

IIf(IsNull([e-mail2]),Null,[e-mail2])

[ssanfu]

Oops, I should have changed the name of the function.

Function AddressBlock$(AName, Addr1, Addr2, City, State, email1, email2, Zip)

should be

Function AddressBlock(AName, Addr1, Addr2, City, State, email1, email2, Zip) As String

OK, I tested both of the functions. They both work (now).

Original

Function AddressBlock(AName, Addr1, Addr2, City, State, email1, email2, Zip) As String

    Dim sA1 As String, sA2 As String, sA3 As String, sA4 As String
    Dim sA5 As String, sA6 As String, sA7 As String, sA8 As String
    Dim sCR As String

    sCR = Chr(13) & Chr(10)    'Carriage return and line feed.

    sA1 = IIf(ISB(AName), "", AName & sCR)
    sA2 = IIf(ISB(Addr1), "", Addr1 & sCR)
    sA3 = IIf(ISB(Addr2), "", Addr2 & sCR)
    sA4 = IIf(ISB(City), "", City & sCR)
    sA5 = IIf(ISB(State), "", State & sCR)
    sA6 = IIf(ISB(email1), "", email1 & sCR)
    sA7 = IIf(ISB(email2), "", email2 & sCR)
    sA8 = "" & Zip

    'Concatenate the strings.
    AddressBlock = sA1 & sA2 & sA3 & sA4 & sA5 & sA6 & sA7 & sA8

End Function

Function ISB(V) As Integer
    ISB = False  ' set default return value

    If IsNull(V) Or V = "" Then
        ISB = True
    End If

End Function

My re-write

Function AddressBlock(AName, Addr1, Addr2, City, State, email1, email2, Zip) As String

    Dim sA1 As String, sA2 As String, sA3 As String, sA4 As String
    Dim sA5 As String, sA6 As String, sA7 As String, sA8 As String
    Dim sCR As String

    sCR = Chr(13) & Chr(10)    'Carriage return and line feed.
   ' could use the following line instead
   '   sCR = vbNewLine
   
    sA1 = IIf(Len(Nz(Trim(AName))) = 0, "", AName & sCR)
    sA2 = IIf(Len(Nz(Trim(Addr1))) = 0, "", Addr1 & sCR)
    sA3 = IIf(Len(Nz(Trim(Addr2))) = 0, "", Addr2 & sCR)
    sA4 = IIf(Len(Nz(Trim(City))) = 0, "", City & sCR)
    sA5 = IIf(Len(Nz(Trim(State))) = 0, "", State & sCR)
    sA6 = IIf(Len(Nz(Trim(email1))) = 0, "", email1 & sCR)
    sA7 = IIf(Len(Nz(Trim(email2))) = 0, "", email2 & sCR)
    sA8 = "" & Zip

    'Concatenate the strings.
    AddressBlock = sA1 & sA2 & sA3 & sA4 & sA5 & sA6 & sA7 & sA8

End Function

You should have something that looks like:

=AddressBlock([AName],[Addr1],[Addr2],[City],[State],[email1],[email2],[Zip])

This is where you should have the names of the two email controls. The controls can be names anything... as long as they are in the correct place.
So if you have the two controls for the emails named "State1" and "State2", then the function call should be:

=AddressBlock([AName],[Addr1],[Addr2],[City],[State],[State1],[State2],[Zip])

If the two controls that have the email addresses are named "cat" and "dog", then the function call would look like:

=AddressBlock([AName],[Addr1],[Addr2],[City],[State],[Cat],[Dog],[Zip])

[mcgeester]

OK! I think I have everything working now. Thanks for your help!

[ssanfu]

You're welcome. Good luck.