[AccessD] Insert a new line in an expression?

Hi Tina,
I use a rather long function as shown below.  I have actually trimmed this
down considerable for your use as it actually does a whole bunch of other
things for me.  For your requirements this should suffice.  It uses
concatenation with nulls. This means it stores contents into variants
instead of strings (although they are named as string for my own use. Eg
strTitle instead of varTitle, change if you prefer).

As I say, it is rather lengthy because I never know what data I am going to
have and need to have a full address constructed correctly.  Hope I have
removed anything useful.

HTH

Max

Function tryIt()
MsgBox pfFormatPersonAddress(11200)
End Function

Function pfFormatPersonAddress(lngPID As Long) As String
  Dim dbs As DAO.Database, rst As DAO.Recordset, sql As String
  Dim bBadPostalArea As Boolean
  bBadPostalArea = False  ' could be overseas
  Dim strAddrCode As String
  Dim lngTitleID As Variant, lngSuffixID As Variant
  Dim lngFKDistrictID As Variant, lngFKTownID As Variant
  Dim lngFKCountyID As Variant, lngFKPostalAreaID As Variant
  Dim lngFKCountryID As Variant

  Dim strTitle As Variant, strSuffix As Variant
  Dim strFirstName As Variant, strSecondName As Variant
  Dim strSurname As Variant

  Dim strHouseName As Variant, strHouseNum As Variant
  Dim strStreet As Variant
  Dim strPostalDistrict As Variant, strIn As Variant
  Dim strCountry As Variant, strCounty As Variant, strTown As Variant
  Dim strDefaultPostCode As String
  Dim strDistrict As Variant
  Dim varAddr As Variant, strLine As Variant
  Dim strOverseasPostCode As Variant

  ' rst variables that are being updated
  Dim strAddrName As Variant
  Dim strAddrMembName As Variant
  Dim strAddrShort As Variant
  Dim strAddrSingle As Variant
  Dim strAddrFull As Variant
  Dim strPostalArea As String
  Dim lngLocationID As Long

  Set dbs = CurrentDb

  sql = "Select * from mcmPeople where PersonID=" & lngPID
  Set rst = dbs.OpenRecordset(sql)
  If rst.EOF Then
    MsgBox "Cannot Find Person With PID: " & lngPID
    GoTo exithere
  End If

  ' Change empty strings to nulls so that empty vars are all set to null
  ' we can then use the '+' sign to avoid having empty lines
  If Nz(rst!FKTitleID, 0) = 0 Then lngTitleID = 0 Else lngTitleID =
rst!FKTitleID
  If Nz(rst!FKSuffixID, 0) = 0 Then lngSuffixID = 0 Else lngSuffixID =
rst!FKSuffixID
  If Nz(rst!FKPostalAreaID, 0) = 0 Then lngFKPostalAreaID = 0 Else
lngFKPostalAreaID = rst!FKPostalAreaID
  If Nz(rst!FKDistrictID, 0) = 0 Then lngFKDistrictID = 0 Else
lngFKDistrictID = rst!FKDistrictID
  If Nz(rst!FKTownID, 0) = 0 Then lngFKTownID = 0 Else lngFKTownID =
rst!FKTownID
  If Nz(rst!FKCountyID, 0) = 0 Then lngFKCountyID = 0 Else lngFKCountyID =
rst!FKCountyID
  If Nz(rst!FKCountryID, 0) = 0 Then lngFKCountryID = 0 Else lngFKCountryID
= rst!FKCountryID


  If Len(Trim(rst!FirstName)) = 0 Or rst!FirstName = "" Then strFirstName =
Null Else strFirstName = rst!FirstName
  If Len(Trim(rst!SecondName)) = 0 Or rst!SecondName = "" Or
IsNull(rst!SecondName) Then
    strSecondName = Null
  Else
    strSecondName = rst!SecondName
  End If

  If Len(Trim(rst!Surname)) = 0 Or rst!Surname = "" Then strSurname = Null
Else strSurname = rst!Surname
  If Len(Trim(rst!HouseName)) = 0 Or rst!HouseName = "" Then strHouseName =
Null Else strHouseName = rst!HouseName
  If Len(Trim(rst!HouseNum)) = 0 Or rst!HouseNum = "" Then strHouseNum =
Null Else strHouseNum = rst!HouseNum
  If Len(Trim(rst!Street)) = 0 Or rst!Street = "" Then strStreet = Null Else
strStreet = rst!Street
  If Len(Trim(rst!PostalDistrict)) = 0 Or rst!PostalDistrict = "" Then
strPostalDistrict = Null Else strPostalDistrict = rst!PostalDistrict
  If Len(Trim(rst!InCode)) = 0 Or rst!InCode = "" Then strIn = Null Else
strIn = rst!InCode
  If Len(Trim(rst!OverseasPostCode)) = 0 Or rst!OverseasPostCode = "" Then
strOverseasPostCode = Null Else strIn = rst!OverseasPostCode


  '==============================================================
  ' NAMES ****************  ' sometime we only have an Initial for the first
name
  If Not IsNull(strFirstName) And Len(strFirstName) = 1 Then
    strFirstName = strFirstName & "."
  End If
  strAddrName = Trim(((strTitle + " ") & (strFirstName + " ") & _
                      (Left(strSecondName, 1) + ". ") & (strSurname + " ") &
(strSuffix + "")))      '& "   (" & rst!PersonID & ")"

  strAddrFull = strAddrName  ' only goes into full address
  If Not IsNull(strHouseName) Then
    strAddrFull = strAddrFull & vbCrLf & strHouseName
  End If

  strLine = (strHouseNum + " ") & (strStreet + " ")
  If Not Nz(strLine) & "!" = "!" Then
    strAddrFull = strAddrFull & vbCrLf & strLine
  End If

  ' get 3rd line of address or do nothing - not even a blank line
  strDistrict = Trim(Nz(DLookup("District", "mcmGeoDistricts", "DistrictID="
& lngFKDistrictID), Null))
  strAddrShort = strAddrShort & strDistrict & vbCrLf
  strAddrSingle = strAddrSingle & (strDistrict + ", ")
  If Not Nz(strDistrict) & "!" = "!" Then
    strAddrFull = strAddrFull & vbCrLf & strDistrict
  End If

  ' get 4th line of address or do nothing - not even a blank line
  strTown = Trim(UCase$(Nz(DLookup("Town", "mcmGeoTowns", "TownID=" &
Nz(lngFKTownID, 0)), " ")))
  strAddrShort = strAddrShort & (strTown + ", ")
  strAddrSingle = strAddrSingle & (strTown + ", ")
  If Not Nz(strTown) & "!" = "!" Then
    strAddrFull = strAddrFull & vbCrLf & strTown
  End If


  ' get 5th line of address or do nothing - not even a blank line

  If lngFKCountyID = 65 And Left(strTown, 6) = "Dublin" Then
    ' Skip if County Dublin and Dublin is in the town
  Else
    strCounty = Trim(Nz(DLookup("County", "mcmGeoCountys", "CountyID=" &
Nz(lngFKCountyID, 0)), Null))
    strDefaultPostCode = Trim(Nz(DLookup("FKPostalAreaID", "mcmGeoCountys",
"CountyID=" & Nz(lngFKCountyID, 0)), ""))
    If Trim(Nz(strTown, "")) <> Trim(Nz(strCounty, "")) Then
      strAddrShort = strAddrShort & (strCounty + ", ")
      strAddrSingle = strAddrSingle & (strCounty + ", ")
      If Not Nz(strCounty) & "!" = "!" Then
        strAddrFull = strAddrFull & vbCrLf & strCounty
      End If
    End If
  End If

  ' See if we can get a default postcode in there if not one held from the
[mcmGeoCountys] Tabls
  If Nz(rst!FKPostalAreaID, 0) = 0 And Len(Trim(strDefaultPostCode)) > 0
Then
    lngFKPostalAreaID = DLookup("PostalAreaID", "mcmGeoPostalAreas",
"PostalArea='" & strDefaultPostCode & "'")
    If lngFKPostalAreaID = 0 Then
      lngFKPostalAreaID = 153  ' ZZ uknown
    End If
    If Not rst!FKPostalAreaID = lngFKPostalAreaID Or
IsNull(rst!FKPostalAreaID) Then
      rst!FKPostalAreaID = lngFKPostalAreaID
    End If
  End If

  ' get 6th line of address or do nothing - not even a blank line
  strPostalArea = Nz(DLookup("Postalarea", "mcmGeoPostalAreas",
"PostalAreaID=" & Nz(lngFKPostalAreaID, 0)), "")
  strLine = Trim(((strPostalArea + "") & (strPostalDistrict + " ") & (strIn
+ " ")))
  If InStr("ZZ/OV/IE/IS/IN", Left(strLine, 2)) > 0 Then
    ' ignore ireland and overseas and unknown
  Else
    'MsgBox lngFKPostalAreaID & vbCrLf & strPostalDistrict & vbCrLf &
strLine
    strAddrShort = strAddrShort & (strLine + ", ")
    strAddrSingle = strAddrSingle & (strLine + ", ")
    If Not Nz(strLine) & "!" = "!" Then
      strAddrFull = strAddrFull & vbCrLf & strLine
    End If
  End If

  strCountry = Nz(DLookup("Country", "mcmGeoCountrys", "CountryID=" &
Nz(lngFKCountryID, 0)), Null)
  If Not InStr("England/Scotland/Wales", strCountry) > 0 Then
    strAddrShort = strAddrShort & strCountry
    strAddrSingle = strAddrSingle & strCountry
    If Not Nz(strCountry) & "!" = "!" Then
      strAddrFull = strAddrFull & vbCrLf & strCountry
    End If
  Else
    strAddrSingle = Trim(strAddrSingle)
    If Right(strAddrSingle, 1) = "," Then strAddrSingle =
Left(strAddrSingle, Len(strAddrSingle) - 1)
    strAddrShort = Trim(strAddrShort)
    If Right(strAddrShort, 1) = "," Then strAddrShort = Left(strAddrShort,
Len(strAddrShort) - 1)
  End If

  pfFormatPersonAddress = strAddrFull

exithere:
  Set dbs = Nothing: Set rst = Nothing
  Exit Function
errhandler:
  Select Case Err.Number
  Case Else
    MsgBox "Address Error: " & Err.Number & vbCrLf & Err.Description
  End Select
  Resume exithere

End Function

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tina Norris
Fields
Sent: Saturday, November 03, 2007 4:10 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Insert a new line in an expression?

It seems that this should be easy, but I'm not getting it.  I have a table
of registered voters with their physical addresses and mailing addresses if
other than the physical address.  The physical address fields were laid out
according to excellent rules, separated into house numbers, directional
prefix, roadway name, roadway type, directional suffix, and residential
extension.  But, the mailing address fields were just MailAddr1, MailAddr2,
MailAddr3, MailAddr4, and MailAddr5 text fields, with no consistent data
entry rules - so there were PO Box numbers in MailAddr1 or MailAddr2, alone
or in combination with city name, state name, and sometimes zip code.  In
order to use the data for mailing, I spent yesterday cleaning up the mailing
address fields so that a "full" street address if any is in MailAddr1, PO
Boxes are in MailAddr2, City, State, and Zip are in MailAddr3, MailAddr4,
and
MailAddr5 respectively.

Now, I am trying to generate a report with these addresses.  In some cases
there are both a street address and a PO Box number, such as 2651 Gunny's
Way NE, PO Box 1534.  But, I don't want a blank line appearing for blank
street addresses or PO Box addresses, so I'm trying to put them together in
an expression. What I want to do is insert a carriage return character after
the street address to put the PO Box on its own print line. 

I have this expression so far:
=Trim(Iif([MailAddr1] Is Not Null, [MailAddr1]&" ","")&[MailAddr2]) which
strings them out nicely, with a space between them.  Instead of the " " I
want to have ASCII 13 or the Access vba equivalent.  I have tried vbCrLf,
but Access sticks square brackets around it and demands a value for it as a
parameter.  (I've tried a bunch of other stuff, too, but just in case this
turns out to be way easy, I am not going to spill the beans about my many
failed efforts.)

How do I do this?

TIA
Tina
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com