Results 1 to 15 of 15
  1. #1
    88keys is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    7

    Simple Question


    Hi,
    I'm pretty good but no expert at access.
    I have what's probably a simple question for this forum;

    I have a client database with a form for entry of each record.
    I have 5 tabs on my form, with the first tab being the Client (company) info, and the remaining four as potentially four (minimally 1) contacts at each individual company.
    The "contact" tabs are named Contact1, Contact2, Contact3, and Contact4, respectively.

    So lets use the Contact1 tab to base my question on.
    The remaining three are set up with the same fields, except they have 2,3,and 4 added to the field names, labels, checkboxes, etc.

    I have three fields and one label that i'm concerned about.
    So using the Contact1 tab, the fields are:
    • Contact1FirstName
    • Contact1LastName
    • Contact1NickName

    And the label is:
    • Contact1Label


    Using the following, If the First name =Joseph, the last name = Smith, and the nick name=Joe, I want the Label to display what's in the Contact1FirstName, Contact1LastName, and Contact1NickName fields as follows:

    Joseph (Joe) Smith

    Notice the parentheses around the nickname.

    To do this, I used the following code in the label:
    =[Contact1FirstName] & " " & "(" & [Contact1Nickname] & ")" & " " & [Contact1LastName]

    This works just fine, except in cases where there is no nickname, the parentheses still appear, like this:
    Joseph () Smith

    My question is, how do I make the parentheses NOT appear when there is no nickname?

    Thank you for any help you can offer!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If 'no nickname' means field is Null, try:

    =[Contact1FirstName] & " " & "(" + [Contact1Nickname] + ")" + " " & [Contact1LastName]

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You can also use the IIf function, which can return nothing of the fieldd is Null:

    =[Contact1FirstName] & " " & IIf(isnull([Contact1Nickname], "", "(" & [Contact1Nickname] & ")" & " " & [Contact1LastName])

    If [Contact1Nickname] is Null, the IIf returns a Zero-length string, otherwise it returns what you had with no change.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Nothing to do with your question but does sound like you are going down the non normalised route which is a bad idea. contacts should be in a separate table.

  5. #5
    88keys is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    7
    This suggestion returns an error, "invalid number of arguments"
    And I can't figure out why.
    I'm referring to the one above which suggests:
    =[Contact1FirstName] & " " & IIf(isnull([Contact1Nickname], "", "(" & [Contact1Nickname] & ")" & " " & [Contact1LastName])

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Missing a closing bracket
    IIf(isnull([Contact1Nickname]),

  7. #7
    88keys is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    7
    Written as below works:
    =IIf(IsNull([Contact1Nickname]),[Contact1FirstName] & " " & [Contact1LastName],[Contact1FirstName] & " " & "(" & [Contact1Nickname] & ")" & " " & [Contact1LastName])

  8. #8
    88keys is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    7
    Yes I tried that but it omitted the last name.
    So,
    I tried this:
    =IIf(IsNull([Contact1Nickname]),[Contact1FirstName] & " " & [Contact1LastName],[Contact1FirstName] & " " & "(" & [Contact1Nickname] & ")" & " " & [Contact1LastName])
    and it works as intended.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Referring to calculation in Post #5:

    The last name was inside the IIF() function
    This seems to work
    Code:
    =[Contact1FirstName] & " " & IIf(isnull([Contact1Nickname]), "", "(" & [Contact1Nickname] & ") ") & [Contact1LastName]

    And I agree with Ajax - you have a non normalized table structure. You need another table for contacts.

  10. #10
    88keys is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    7
    Thank you all for your help.
    It's greatly appreciated and I will try the alternate code shown in the last post even though what I have is working now, Steve's code is simpler.
    Another Question; Why should the contacts be in a different table?
    Just curious thanks.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to allow an unlimited number of contacts for each Client, definitely use a related table to store the contacts. Someday you might encounter that one client with 6 contacts. And since I doubt every client will have 5 contacts, probably means a lot of blank fields. Your structure might be adequate for your needs but if you ever encounter requirement to select specific contact, it won't be. For instance, if you had to log calls to client and needed to document who was spoken with, might want to be able to select contact from a combobox dropdown.

    It is a balancing act between normalization and ease of data entry/output. "Normalize until hurts, denormalize until it works."
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    88keys is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    7
    Great Points!
    I guess at this point, since I have about 400 companies in my database, with varying numbers of contacts for each, that to implement that, I would need to run a query, get a list of all of them, get that into an excel SS or CSV, then get that into a new "Contacts" table.
    I could still have the tabs to apply them to on the main form.
    Another thing that's been on my mind;
    I've been wanting to assign priorities at each company to each contact. There's always, a primary, secondary, etc. And I'ts not uncommon for someone to leave a company, then there is a new primary contact. OR sometimes the first person that I talk to at a company, doesn't end up being the primary contact. the order changes quite often. I'm sure I'll have questions about how to assign those levels of priority. I like having the contacts on the company form as a part of each company record (at least visually). But would like to have the ability to have a drop down on each contact record (tab) that assigns the priority and thus moves the priority contact to the "Contact 1" tab, and #2 to #2 and so forth.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't think Excel or CSV would be needed to create the contacts table but that certainly is one approach to accomplish.

    The dependent table could have fields for Priority as well as IsActive. The multiple tabs approach would not be practical if unlimited number of associated contacts is allowed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    88keys is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    7
    The reason I mentioned Excel or CSV is because I already have a few hundred contacts in the database. And running a Query then organizing the contacts in a SS or CSV, might be a quick way to get them into a table, rather than creating a new table and hand entering them all.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A UNION query can rearrange the multiple contact fields into a normalized table structure. Save those records to a new table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple sql question
    By mrdave9 in forum Access
    Replies: 4
    Last Post: 07-13-2015, 02:16 PM
  2. Simple Question I'm sure
    By scarruth2 in forum Access
    Replies: 1
    Last Post: 08-26-2012, 03:07 PM
  3. Simple VBA Question
    By koper in forum Access
    Replies: 1
    Last Post: 01-20-2011, 12:07 PM
  4. Simple question?
    By roads.zx in forum Access
    Replies: 0
    Last Post: 10-15-2009, 04:56 PM
  5. Very simple question!
    By fiddley in forum Programming
    Replies: 2
    Last Post: 04-28-2009, 02:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums