Prev: Multiple Totals Columns in a Pivot Table
Next: #Error
From: DC1 on 20 May 2010 18:15
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

From: KARL DEWEY on 20 May 2010 19:21
Try this --
Contact Name: IIf([Female Surname] Is Null OR [Male Surname] Is Null,
Nz([Female Surname], "") & Nz([Male Surname],""), [Male Surname] & " and
" & [Female Surname])


--
Build a little, test a little.


"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
>
> .
>
From: John Spencer on 21 May 2010 08:56
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
>
 | 
Pages: 1
Prev: Multiple Totals Columns in a Pivot Table
Next: #Error