Insert Linebreak in Ms Access Query

Question

Hi I am developing a PHP application that unfortunately has to use MS Access as a backend, and I am not 100% up to date with access compared to mysql.

I am trying to produce an address label field in a customer table. The idea being is to have an address line (max of 7), to include line if not null and then line break for next line, so you get a postage label style without blank lines.

So far I have this in my query...

SELECT
tblCompany.CompanyId,
tblCompany.CompanyTypeId,
tblCompany.CompanyName,
    IIF(Len(PafAddress.line1)>0,PafAddress.line1,"") & " "  &
    IIF(Len(PafAddress.line2)>0,PafAddress.line2,"") & " " &
    IIF(Len(PafAddress.line3)>0,PafAddress.line3,"") & " " &
    IIF(Len(PafAddress.line4)>0,PafAddress.line4,"") & " " &
    IIF(Len(PafAddress.Line5)>0,PafAddress.line5,"") & " " &
    IIF(Len(PafAddress.post_town)>0,PafAddress.post_town,"") & " " &
    IIF(Len(PafAddress.county)>0,PafAddress.county,"") & " " &
    IIF(Len(PafAddress.postcode)>0,PafAddress.postcode,"") 
    AS AddressLabel,
FROM tblCompany
LEFT OUTER JOIN PafAddress ON tblCompany.PafAddressId = PafAddress.id

However this just puts a couple of spaces next to each entry rather than a line break, I have tried

& CHAR(13)+CHAR(10) &

But just keep getting

UNDEFINED FUNCTION CHAR

Can anyone help?

Spelling, CHR, not CHAR: http://stackoverflow.com/questions/2203710/is-there-n-equivalent-in-vbscript/2203735#2203735 Also, check out how using plus (+) as a concatenator works with MS Access. — Fionnuala, Mar 02 '15 at 08:15

score 2 · Answer 1 · answered Mar 02 '15 at 11:34

Empty fields are not zero-length but Null, and you have to use Access SQL and function names:

SELECT
tblCompany.CompanyId,
tblCompany.CompanyTypeId,
tblCompany.CompanyName,
    PafAddress.line1 &
    (Chr(13) + Chr(10) + PafAddress.line2) &
    (Chr(13) + Chr(10) + PafAddress.line3) &
    (Chr(13) + Chr(10) + PafAddress.line4) &
    (Chr(13) + Chr(10) + PafAddress.line5) &
    (Chr(13) + Chr(10) + PafAddress.post_town) &
    (Chr(13) + Chr(10) + PafAddress.county) &
    (Chr(13) + Chr(10) + PafAddress.postcode)
AS AddressLabel,
FROM tblCompany
LEFT OUTER JOIN PafAddress 
    ON tblCompany.PafAddressId = PafAddress.id

This inserts a new line before fields that are not empty.

I think if `PafAddress.line1` were empty this code would output an empty line to `AddressLabel` before any others. — Matt Hall, Mar 02 '15 at 15:19
At least one line will be filled and that will be the first. — Gustav, Mar 02 '15 at 17:10

Matt Hall · Answer 2 · 2015-03-02T17:43:01.417

Ugly as hell, but this should work for the AddressLabel field:

Left(IIF(Not(IsNull(PafAddress.line1)),PafAddress.line1 & Chr(13) & Chr(10),"") & IIF(Not(IsNull(PafAddress.line2)),PafAddress.line2 & Chr(13) & Chr(10),"") & IIF(Not(IsNull(PafAddress.line3)),PafAddress.line3 & Chr(13) & Chr(10),"") & IIF(Not(IsNull(PafAddress.line4)),PafAddress.line4 & Chr(13) & Chr(10),"") & IIF(Not(IsNull(PafAddress.Line5)),PafAddress.line5 & Chr(13) & Chr(10),"") & IIF(Not(IsNull(PafAddress.post_town)),PafAddress.post_town & Chr(13) & Chr(10),"") & IIF(Not(IsNull(PafAddress.county)),PafAddress.county & Chr(13) & Chr(10),"") & IIF(Not(IsNull(PafAddress.postcode)),PafAddress.postcode & Chr(13) & Chr(10),""), Len(IIF(Not(IsNull(PafAddress.line1)),PafAddress.line1 & Chr(13) & Chr(10),"") & IIF(Not(IsNull(PafAddress.line2)),PafAddress.line2 & Chr(13) & Chr(10),"") & IIF(Not(IsNull(PafAddress.line3)),PafAddress.line3 & Chr(13) & Chr(10),"") & IIF(Not(IsNull(PafAddress.line4)),PafAddress.line4 & Chr(13) & Chr(10),"") & IIF(Not(IsNull(PafAddress.Line5)),PafAddress.line5 & Chr(13) & Chr(10),"") & IIF(Not(IsNull(PafAddress.post_town)),PafAddress.post_town & Chr(13) & Chr(10),"") & IIF(Not(IsNull(PafAddress.county)),PafAddress.county & Chr(13) & Chr(10),"") & IIF(Not(IsNull(PafAddress.postcode)),PafAddress.postcode & Chr(13) & Chr(10),"")-2)) AS AddressLabel,

This will add a traling line break no matter how many lines are filled. — Gustav, Mar 02 '15 at 17:12

Insert Linebreak in Ms Access Query

2 Answers2