Building an Address String

[Samantha]

I am trying to build an address string based off of my form. It has the following fields; ServiceAddress (FK), ID (PK), BillToCompany, BillCareOf, BillAttn, BillAddress, BillCity, BillingZip, BillBoxNumber.

What I would like to happen is the BillToCompany go on First and if that is blank I want it to populate with the string value of AddyLineVar and nothing else. Otherwise- following BillToCompany, I would like BillCareOf to follow on the next line skipping if it is blank, then BillAttn planning for nulls as well.

Currently if BillToCompany is blank then the code stops running. If there is a value in BillToCompany it proceeds almost how it should, it skips the BillCareOf & BillAttn.

'Start building BillingAddress
If IsNull([Billing Information].Form![BillToCompany]) Then
BillingAddress = AddyLineVar
Else
BillingAddress = ([Billing Information].Form![BillToCompany])
 
'Add c/o on after Company.
If IsNull([Billing Information].Form![BillCareOf]) Then
AddyLineVar = AddyLineVar & vbCrLf & [Billing Information].Form![BillCareOf]
End If
 
'Add Attn on after Company.
If IsNull([Billing Information].Form![BillAttn]) Then
AddyLineVar = AddyLineVar & vbCrLf & [Billing Information].Form![BillAttn]
End If
 
'Add line break and Address lines.
BillingAddress = BillingAddress & vbCrLf & ([Billing Information].Form![BillAddress])
'Tack on line break then city, state, and zip.
BillingAddress = BillingAddress & vbCrLf & ([Billing Information].Form![BillCity]) & ", "
BillingAddress = BillingAddress & ([Billing Information].Form![State]) & " " & ([Billing Information].Form![BillingZip])

Many Thanks for Looking!
Samantha

 

[MSAccessRookie]

You do not appear to be adding the C/O and the Attn Lines to your Billing Address. Instead you are adding them to a temporary holding variable named AddyLineVar and ignoring them later. Based on what I see, you might be able to get away without using AddyLineVar at all if you substitute BillingAddress wherever you have AddyLineVar. If AddyLineVar is used for other purposes, then you could substitute AddyLineVar wherever you have BillingAddress, and add a new line at the end to set BillingAddress to the updated value of AddyLineVar. Not sure if this is the answer, but it could be a good start.

-- Rookie

 

[Samantha]

Thanks Rookie,

Perhaps it would help if I explain further AddyLineVar is another address block stringed together. It is the Property Manager involved in the project. Essentially we are in construction so an inital proposal would go to the property manager while an invoice would go to the building owner if we have the information available when we don't it is sent to the property manager. So the whole code which sends everything to an Excel Spreadsheet is as follows:

Private Sub cmdMergeXLbttn_Click()
On Error GoTo Error_Handler
'Declare a variable named MySheetPath as string
Dim MySheetPath As String
'set file path to actual sheet
MySheetPath = "O:\Access\ProjectSheet.xltx"
'Set up object variables to refer to Excel and Objects
Dim XL As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim strServiceAddress As String
Dim ctl As Control
Dim varItem As Variant
Dim BillingAddress As String
Dim AddyLineVar As String
Dim CompanyAddress As String
'Open an instance of Excel, open the workbook.
Set XL = CreateObject("Excel.Application")
Set XLBook = GetObject(MySheetPath)
'Make sure everything is visible
XL.Visible = True
XLBook.Windows(1).Visible = True
'Define top sheet in Workbook as XLSheet
Set XLSheet = XLBook.Worksheets(1)
'Set Company Address
CompanyAddress = [Company] & vbCrLf & ([sfrmContacts].[Form]![Address]) & vbCrLf & ([sfrmContacts].[Form]![City]) & ", " & ([sfrmContacts].[Form]![State]) & " " & ([sfrmContacts].[Form]![ZipCode])
'Start building BillingAddress
    If IsNull([Billing Information].Form![BillToCompany]) Then
       BillingAddress = AddyLineVar
    Else
       BillingAddress = ([Billing Information].Form![BillToCompany])
       
   'Add c/o on after Company.
        If IsNull([Billing Information].Form![BillCareOf]) Then
            AddyLineVar = AddyLineVar & vbCrLf & [Billing Information].Form![BillCareOf]
        End If
   
   'Add Attn on after Company.
        If IsNull([Billing Information].Form![BillAttn]) Then
            AddyLineVar = AddyLineVar & vbCrLf & [Billing Information].Form![BillAttn]
        End If
               
    'Add line break and Address lines.
    BillingAddress = BillingAddress & vbCrLf & ([Billing Information].Form![BillAddress])
    'Tack on line break then city, state, and zip.
    BillingAddress = BillingAddress & vbCrLf & ([Billing Information].Form![BillCity]) & ", "
    BillingAddress = BillingAddress & ([Billing Information].Form![State]) & " " & ([Billing Information].Form![BillingZip])
    
'Start building AddyLineVar, by dealing with blank last name fields.
    If IsNull([sfrmContacts].[Form]![Last]) Then
        AddyLineVar = [Company]
        'Just set salutation to generic.
        SalutationVar = "Sir or Madam"
    Else
        AddyLineVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last])
        
        'Add Company on after name.
        If Not IsNull([Company]) Then
            AddyLineVar = AddyLineVar & vbCrLf & [Company]
        End If
        
        'Salutation will be customer's last name
        SalutationVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![Last]) & ", "
    End If
        
    'Add line break and Address lines.
    AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![Address])
    'Tack on line break then city, state, and zip.
    AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![City]) & ", "
    AddyLineVar = AddyLineVar & ([sfrmContacts].[Form]![State]) & " " & ([sfrmContacts].[Form]![ZipCode])
    CustCompany = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last]) & vbCrLf & [Company]
    
XLSheet.Range("DateGen") = Date
XLSheet.Range("ProjectNumber") = JobNumber
XLSheet.Range("CompanyAdd") = CompanyAddress
XLSheet.Range("Contact") = ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last])
XLSheet.Range("Phone") = ([sfrmContacts].[Form]![Phone])
XLSheet.Range("Fax") = ([sfrmContacts].[Form]![BusinessFax])
XLSheet.Range("ProjectDescription") = ProjectDescription
XLSheet.Range("ServiceAdd") = Me!ServiceAddress.Value '-returned only first selection when combo box is used text box transfers whole string
XLSheet.Range("City") = City
XLSheet.Range("State") = State
XLSheet.Range("ProjectType") = ([sfrmProjectType].[Form]![ProjectType])
XLSheet.Range("PurchaseOrder") = PONumber
XLSheet.Range("OnsiteContact") = [sfrmJobInformation].Form![OnsiteContact]
XLSheet.Range("BillTo") = BillingAddress
XLSheet.Range("CellPhone") = [sfrmJobInformation].Form![Mobile Phone]

MsgBox "Your Project Information Sheet is Ready." & vbCrLf & "Please re-name your document and save to the job file.", vbOKOnly, "Successful"
Exit_Procedure:
Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application." & " Please contact your technical support with the following information:" & vbCrLf & vbCrLf & "Error Number" & " " & Err.Number & ", " & Err.Description, Buttons:=vbCritical
    
    Resume Exit_Procedure
   End If
End Sub