Results 1 to 15 of 15
  1. #1
    DecaturLady is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26

    IIF query puzzle

    Dear Friends,
    This newbie has been struggling for days trying to create a concatenation query utilizing the IIF function.
    My data are 68-year-old high school graduates. The fields involved are: GOESBY, LNAME, and MARRIEDNAME (lname is lastname). I am attempting to create a field called: NAMETODAY.



    Each field contains one name element. I made MarriedName a required field and entered a space in each blank field, thus creating a null field.

    The query affects females in the class, as men generally keep the name they had at birth.
    Gender Goesby LName MarriedName NameToday
    F Penny McMaker Baker Penny Baker
    F Reesa Rainmaker Reesa Rainmaker
    M Fred Freeloader Fred Freeloader

    · Penny married Tom Baker in a lovely wedding and is now known as Penny Baker.
    · Reesa never married and has remained Reesa Rainmaker.
    · It matters not what Fred did in the matrimonial department; as a male he will continue to use Freeloader as his last name.
    So, my query needs to take the first name, then evaluate the married name. If married name is populated, use that data. If it is null, use the last name data.

    This is my query. The error keeps say parenthesis, vertical bar, etc. is out of place. I know it is a simple fix. I just can’t find it.!

    NameToday: [1- Names]![GoesBy] &" " & IIf([1- Names]![MarriedName]="Null",[1- Names]![Lname])


    PS: as a newbie, I can’t do codes. And thanks to all of you for your kindly help….

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    How about:

    NameToday: [1- Names]![GoesBy] & " " & Nz([1- Names]![MarriedName],[1- Names]![Lname])

    In the long run, spaces and symbols in objects are not worth the bother.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    By the way, there are two problems with your effort. First, nothing is "=" to Null; think of Null as "unknown". You could have used the IsNull() function. Second, your IIf() doesn't have the third argument, what to return if false. It could have looked like:

    IIf(IsNull([1- Names]![MarriedName]), [1- Names]![Lname], [1- Names]![MarriedName])

    But obviously the Nz() function is simpler.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    It looks like you want to put the formula into table field NameToday. Quite a lot of not-to-do rules violated here

    1. Don't use any special characters except underscore in table or field names;
    2. Don't have calculated values in your table without dire need for it;
    3. Don't use formulas in your tables.

    You can always calculate current names whenever you need them. P.e.
    a) by query: SELECT Gender, GoesBy, LName, MarriedName, GoesBy & " " & Nz(MarriedName,Lname) AS [CurrentName] FROM tblNames
    b) by formula in form control:
    =Me.txtGoesBy & " " & Nz(Me.txtMarriedName, Me.txtLName) // uses active row form controls as source
    or
    =[Me].[GoesBy] & " " & Nz([Me].[MarriedName], [Me.].[LName]) // uses table row field values in form as source.
    Last edited by ArviLaanemets; 10-20-2017 at 05:30 AM.

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by ArviLaanemets View Post
    It looks like you want to put the formula into table field NameToday.
    Why do you think that? Thread is in the query forum and OP states "This is my query".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    DecaturLady is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26
    Originally Posted by ArviLaanemets
    It looks like you want to put the formula into table field NameToday.

    WRONG!!!

    This is a concatenation expression in a query. There are several similar expressions already in place. As a newbie, this is my first experience with "If/then" statements. Three fields in the TABLE
    [1- Names] which this expression utilizes: GOESBY, LNAME & MARRIEDNAME. Data from other tables will be added shortly (phone #'s, addresses, etc.).

    SO, my final problem is why LNAME is not returned when using the expression.
    Attached Thumbnails Attached Thumbnails Lname missing.png  
    Last edited by DecaturLady; 10-20-2017 at 09:58 AM. Reason: add attachment

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't see the MarriedName field, or what your expression is. Is it possible MarriedName isn't Null, just a ZLS?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    DecaturLady is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26
    yesterday, following recommendations in a reference book I have, I made Married Name a required field, then entered a space in every empty field. Of course, as I left the field, the space disappeared. Ref Book said this was the best method of creating a Null field.

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You probably misunderstood something. A required field can't be Null. You'll have to switch to the IIf() I suggested and test for "".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by pbaldy View Post
    Why do you think that? Thread is in the query forum and OP states "This is my query".
    Because the formula [1- Names]![GoesBy] &" " & IIf([1- Names]![MarriedName]="Null",[1- Names]![Lname]) isn't in query syntax!

  11. #11
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you miss the "NameToday: " at the beginning?
    Quote Originally Posted by DecaturLady View Post
    NameToday: [1- Names]![GoesBy] &" " & IIf([1- Names]![MarriedName]="Null",[1- Names]![Lname])
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    DecaturLady is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26
    Dear Wino,
    you may note I started with IIF and I think I had other syntax problems (such as the Quotes around NULL.

    I've attached the "design view" of my names table. You will note all the field names in my expression are in the 1-Names table.

    While you think, I've removed "required" from MarriedName's properties and have in the table, started entering "", which disappears up field exit.

    By the way, what is ZLS?

    p
    Attached Thumbnails Attached Thumbnails Names table.png  

  13. #13
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, and I gave you the fixed version of that too. You can't test for Null if the field is required, as it can't possibly be Null. ZLS stands for Zero Length String, which you'd test with "". So try this, which I think will handle both ZLS and a space:

    IIf(Len(Trim([1- Names]![MarriedName])) = 0, [1- Names]![Lname], [1- Names]![MarriedName])
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    DecaturLady is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26

    Solved!

    Brilliant Wino!!!!

    That did it!

    thanks for all the effort you put into my project. I hope I learned something too!!


  15. #15
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! And I had some Cabernet just now with dinner.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Help with interesting VBA Code puzzle
    By johnson8809 in forum Import/Export Data
    Replies: 5
    Last Post: 04-22-2015, 01:45 PM
  2. Replies: 4
    Last Post: 10-08-2013, 09:49 PM
  3. Iff Puzzle VBA and DLookup
    By Ruegen in forum Forms
    Replies: 5
    Last Post: 08-20-2013, 12:25 AM
  4. VB Strategy Games - Circle Puzzle and Others
    By pkstormy in forum Code Repository
    Replies: 4
    Last Post: 03-05-2011, 03:18 AM
  5. Unique Puzzle
    By Christina G in forum Database Design
    Replies: 0
    Last Post: 03-14-2009, 06:37 PM

Tags for this Thread

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