Leave Null spouses out

  • Thread starter Thread starter jake
  • Start date Start date
J

jake

I have a table called survivors. It contain First_Name, Spouse,
Last_Name,

I use this info in a report called "family" I want to print out the
survivors as follows:
Jon (Larraine) Rose

if their is not a spouse
Jon Rose

How can I accomplish this?
Thank you. Jake
 
jake said:
I have a table called survivors. It contain First_Name, Spouse,
Last_Name,

I use this info in a report called "family" I want to print out the
survivors as follows:
Jon (Larraine) Rose

if their is not a spouse
Jon Rose

How can I accomplish this?
Thank you. Jake
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use string concatenation:

SELECT First_Name & IIf(IsNull(Spouse)," ", " " & Spouse & " ") &
Last_Name

The IIf() function checks of the Spouse column is NULL. If it is, it
just inserts a space. If it isn't, it inserts a space and the spouse
value and another space.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnHBoIechKqOuFEgEQLf/gCg7Str96qt0Djh1bg7710BtPI7KiQAn1oa
uuH0lZWMznY6hrBd5ZFnju5B
=HFms
-----END PGP SIGNATURE-----
 
I have a table called survivors. It contain First_Name, Spouse,
Last_Name,

I use this info in a report called "family" I want to print out the
survivors as follows:
Jon (Larraine) Rose

if their is not a spouse
Jon Rose

How can I accomplish this?
Thank you. Jake
Click to expand...

MG's suggestion will work, but there's an Access-specific "tricky"
(i.e. not generally applicable so use with care) solution using the
fact that & and + both concatenate strings; they differ in that +
propagates NULLS. Try

[First_Name] & ("(" + [Spouse] + ")") & " " & [Last_Name]

The parenthesized expression will evaluate to NULL if Spouse is NULL.

John W. Vinson[MVP]
 
Back
Top