Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts
    The answer is probably in here but I'm not sure what to look on.

    In my database, I have one field for Mother and one for Father, as well as fields for last name, address, etc.

    In most cases, there is a Mother and a Father, which makes it simple. I am using the following as a control in a text box in my reports, =Trim([Mother1stName] & " & " & [Father1stName] & " " & [last Name]), which would show, for example, Mary & Bob Jones.

    How do I set it up to display just the First and Last Name, when there is only a Mother or a Father? I just want it to show the First Name, blank space, then Last Name. I know it would be some kind of an IIF statement and I played with it and couldn't get it to work properly. Any suggestions? Thanks.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Try this expression:
    IIF(IsNull([Mother1stName]),[Father1stName] & " " & [Last Name],IIF(IsNull([Father1stName]),[Mother1stName] & " " & [Last Name],[Mother1stName & " & " & [Father1stName] & " " & [LastName]))

    I left off the Trim since you are constructing a string that should never have leading or trailing spaces. If you are concerned about that, Access will always trim trailing spaces during data entry, so the real worry is leading spaces. I would use an audit to find those and remove them in your data if they occur, as they invariably cause funny things to happen when you sort or use criteria in a query. Also, your stucture doesn't support a different last name for each parent. We typically try to design that into systems where we are tracking people and relationships.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts
    Thanks Wendell, that was perfect, exactly what I needed.
    Surprisingly the different last name has not been a big issue for us. I do have a comments section and when it does come up, I put it there. I know I will have to address that some day.
    I would have fumbled around for days trying to find the solution. I really appreciate your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •