Warp text in an SQL statement

  • Thread starter Thread starter Cameron Piper
  • Start date Start date
C

Cameron Piper

I am sure this is very easy but I have not been able to
find anything even remotely close at google. Will
someone please tell me how to wrap text in an SQL
statement so that the statement output shows up on two
lines.

In the following statement I would like the municipality,
state, and zip code to appear on a second line:

SELECT tblProperty.StreetNum, tblProperty.StreetName,
tblProperty.Municipality, tblProperty.ZipCode,
tblproperty.streetnum & " " & tblproperty.streetname & ""
& tblproperty.municipality & ", " & tblproperty.state
& " " & tblproperty.zipcode AS EntireAddress
FROM tblProperty;

right now the Entire Address field shows up like:
123 Main Street Anytown, MN 12345

I would like it to show up as:
123 Main Street
Anytown, MN 12345

Also if possible I would like to add a couple of fields
that are at times null to the query. example (street
direction: main st "north"). Is it possible to add these
fields with a condition that they are only shown if they
are not null.

I know that this can be done in filtering an sql
statement, but I do not know how to do this when you are
defining a combined field.

Any help would be greatly appreciated.

Cameron Piper
(e-mail address removed)
 
Cameron Piper said:
I am sure this is very easy but I have not been able to
find anything even remotely close at google. Will
someone please tell me how to wrap text in an SQL
statement so that the statement output shows up on two
lines.

In the following statement I would like the municipality,
state, and zip code to appear on a second line:

SELECT tblProperty.StreetNum, tblProperty.StreetName,
tblProperty.Municipality, tblProperty.ZipCode,
tblproperty.streetnum & " " & tblproperty.streetname & ""
& tblproperty.municipality & ", " & tblproperty.state
& " " & tblproperty.zipcode AS EntireAddress
FROM tblProperty;

right now the Entire Address field shows up like:
123 Main Street Anytown, MN 12345

I would like it to show up as:
123 Main Street
Anytown, MN 12345

Also if possible I would like to add a couple of fields
that are at times null to the query. example (street
direction: main st "north"). Is it possible to add these
fields with a condition that they are only shown if they
are not null.

I know that this can be done in filtering an sql
statement, but I do not know how to do this when you are
defining a combined field.

Any help would be greatly appreciated.

Cameron Piper
(e-mail address removed)
Click to expand...

Try:

SELECT
StreetNum,
StreetName,
Municipality,
ZipCode,
StreetNum & " " & StreetName & (" "+StreetDirection)
& Chr(13) & Chr(10)
& Municipality & ", " & State & " " & ZipCode
AS EntireAddress
FROM
tblProperty;

The combination Chr(13) & Chr(10) is a carriage-return/line-feed, which
is treated as a new-line marker. Using the plus sign (+) for
concatenation with a potentially Null field takes advantage of a trick:
(Null + anything) yields Null.
 
Back
Top