View Single Post
  #3  
Old May 21st, 2010, 01:56 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Querying 2 field unless duplicated

IIF([Female Surname] = [Male Surname],[Female Surname]
, MID((" AND " + [Male Surname]) & (" AND " + [Female Surname]),6))

The third section of the IIF relies on the fact that in Access the & and +
concatenation operators work differently. The + operator propagates nulls
while the & operator treats nulls as if they were zero-length strings ("").
If Male Surname is Null == the first section returns Null.
== the second section returns " AND Smith" which is added to the null with the
& operator and you end up with " AND Smith"
== The mid chops off the leading " AND "
== Final Result "Smith"

Female surname Null
== the Second section returns Null.
== the First section returns " AND Jones " which is added to the null with the
& operator and you end up with " AND Jones"
== The mid chops off the leading " AND "
== Final Result "Jones"

Both names have a value
== Concatenation operation returns " AND Jones AND Smith"
== The Mid function chops of the leading " AND "
== Final results "Jones and Smith"

(I must admit that I picked up this trick from posting by Marshall Barton.)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

DC1 wrote:
New at this, so please bear with me:

I am looking to merge 2 fields unless duplicated. The fields are in the same
row in the same table Female Surname & Male Surname and I have no problem
when the Surnames are different, I think.. (seems a tad convoluted). e.g.,

FemaleSurname and MaleSurname as per below

Contact Name: IIf(IsNull([Female Surname]),IIf(IsNull([Male Surname]),[Male
Surname]),IIf(IsNull([Male Surname]),[Female Surname],[Male Surname] & " and
" & [Female Surname]))

but where the surnames are the same I would like to show it just once.

Can you advise please
thanks
DC1