If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Querying 2 field unless duplicated
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 |
#2
|
|||
|
|||
Querying 2 field unless duplicated
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 . |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|