Northwind Database VBA BusinessAddress Question (1 Viewer)

[SIman]

Hi all, this is my first post so please be gentle with me
I am currently looking at the Northwind database and wondering if it's a good candidate for modifying for my needs. I thought I was doing OK until I found a string that I just can't work out where it's created!
If you know the Northwind database then: The table 'companies' has fields 'address', 'city' and 'zip'. If you open form frmCompanyList there is a column called 'BusinessAddress' that is the address, city and zip together in one string.
My problem: I can't find what on earth creates this string. I have searched all the VBA (I think) and can only find a reference to BusinessAddress in a separate private sub procedure.
If anyone knows the database template and knows (or can instantly spot) where the field is created, I'd be very grateful as it's driving me nuts!
Many thanks,
Si

 

[arnelgp]

check the query (qryCompanyList) in Design view of the query if this is the recordsource of the form.

 

[Gasman]

Are we talking about the latest Northwind?
Just had a look and it could be?
In the query @arnelgp mentioned.

SELECT Companies.CompanyID, Companies.CompanyName, CompanyTypes.CompanyType, Companies.BusinessPhone, Companies.Address, Companies.City, Companies.StateAbbrev, Companies.Zip, Companies.Website, Companies.Notes, TaxStatus.TaxStatus, Companies.AddedBy, Companies.AddedOn, Companies.ModifiedBy, Companies.ModifiedOn, [Address] & Space(2) & [City] & ", " & [StateAbbrev] & Space(2) & [Zip] AS BusinessAddress, Companies.CompanyTypeID, Companies.StandardTaxStatusID
FROM TaxStatus INNER JOIN (CompanyTypes INNER JOIN Companies ON CompanyTypes.CompanyTypeID = Companies.CompanyTypeID) ON TaxStatus.TaxStatusID = Companies.StandardTaxStatusID
ORDER BY Companies.CompanyName;

 

[SIman]

It's defined in the query! Many thanks for quick help arnelgp!!!! Have a great day.

 

[GPGeorge]

In The Northwind databases, you'll find yellow information icons in many places.

These are links to online help that explains features we incorporated in Northwind.

 

[tvanstiphout]

One other suggestion: if you had an add-in such as this one you can do a deep "Find" operation for the word "BusinessAddress", and it would list all occurrences, including the query discussed above.

 

[Ken Sheridan]

In the query the business address is computed by the concatenation of the relevant columns from the table in an expression. If you do have occasion to concatenate values in this way you might find the following function useful:

Public Function ConcatValues(strSeparator As String, ParamArray arrVals())
 
    ' Pass this function a character or characters
    ' to be used as the separator followed by the values to be combined
    ' For example: strFullName =
    ' ConcatValues(" ",FirstName,MiddleName,LastName)
   
    Dim X As Integer, strLine As String
   
    For X = 0 To UBound(arrVals)
        If Not IsNull(arrVals(X)) And Trim(arrVals(X)) <> "" Then
          strLine = strLine & strSeparator & arrVals(X)
        End If
    Next
     
    ' remove leading separator character(s)
    ConcatValues = Mid(strLine, Len(strSeparator) + 1)
 
End Function

This function concatenates the values from the columns whose names are passed into it. If a column position in a row is Null that column will be ignored, and the redundant space (or whatever) delimiter suppressed. It is adapted from the following function published by Microsoft many years ago:

Public Function CanShrinkLines(ParamArray arrLines())

    ' Pass this function the lines to be combined
    ' For example: strAddress =
    ' CanShrinkLines(Name, Address1, Address2, City, State, Zip)
    Dim X As Integer, strLine As String
   
    For X = 0 To UBound(arrLines)
        If Not IsNull(arrLines(X)) And Trim(arrLines(X)) <> "" Then
          strLine = strLine & vbCrLf & arrLines(X)
        End If
    Next
     
    ' remove leading carriage return/line feed
    CanShrinkLines = Mid(strLine, 3)
 
End Function

This function concatenates the values, and returns them as a string expression in which the values are separated by carriage returns/line feeds, hence the name of the function. Nulls are also suppressed, in this case eliminating the redundant blank lines.