Results 1 to 6 of 6
  1. #1
    Kelsallison is offline Novice
    Windows 8 Access 2016
    Join Date
    Jun 2018
    Posts
    2

    Iif statement in query returning ID number

    Hello, I have an Iif statement in a query that is using a combo box field, however it is returning the ID number instead of the name. Below is the statement, any suggestions? Thank you!

    Taught By: IIf([Purchases]![Item Purchased]![Value]=2,[Purchases]![Pro/Staff Member Name]![Value], )," ")

  2. #2
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    The ID value being returned is the bound column of the combo box (which is as it should be). In some scenarios you can retrieve other values from a combo box by referencing the Column property. This is a zero based index, so the first column is Column(0), the second is Column(1), etc. However, you can't directly reference the Column property of a combo box from a query. There are a couple of options;

    1) Use a DLookup in your query to return the correct value directly from the table (the same table that is the source for the combo box). Example;

    Taught By: IIf([Purchases]![Item Purchased]![Value]=2, DLookup("StaffMemberName", "SomeTable", "SomeField = " & [Purchases]![Item Purchased]), "")

    * Correct the naming in the above DLookup to reflect your actual table/Field names.
    * Depending on the data type of the field being referenced in the criteria, you may need to add delimiters.

    2) Place a hidden text box on the form, in the control source of that text box put =
    [Pro/Staff Member Name].Column(1), then reference that text box in your query;

    Taught By: IIf([Purchases]![Item Purchased]![Value]=2,[Purchases]![YourHiddenTextBox],"")

    *If needed, correct the Column numbering above to whichever column in the combo box holds the name



    BTW - Value is the default property of these types of controls, so don't need to explicitly reference it;

    [Purchases]![Item Purchased]![Value] is the same as [Purchases]![Item Purchased]

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Kelsallison View Post
    Hello, I have an Iif statement in a query that is using a combo box field
    To me, this sounds like there is a LOOK UP FIELD in table "Purchases". (not a good idea - see The Evils of Lookup Fields in Tables)
    This is why experienced programmers never use Look Up FIELDS.


    It is a little confusing (without knowing anything about the design)...the column alias is "Taught By", looking at the table "Purchases", trying to get a name.
    Is [Pro/Staff Member Name] a FK to a table of Staff Members?

    Why not add the Staff Members table to the query , link the PK/FK fields and add the First & Last names to the query design grid?


    --------------------------------
    Suggestions about naming objects:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not use Look up FIELDS, Multi-Value fields or Calculated fields in tables.
    Do not begin object names with a number.


    Object names include: fields, tables, queries, forms, reports

  4. #4
    Kelsallison is offline Novice
    Windows 8 Access 2016
    Join Date
    Jun 2018
    Posts
    2
    Thank you so much for your response. What do experienced programmers use instead of Lookup fields? Just a regular text field? Thank you again for your help, I greatly appreciate it!

    Quote Originally Posted by ssanfu View Post
    To me, this sounds like there is a LOOK UP FIELD in table "Purchases". (not a good idea - see The Evils of Lookup Fields in Tables)
    This is why experienced programmers never use Look Up FIELDS.


    It is a little confusing (without knowing anything about the design)...the column alias is "Taught By", looking at the table "Purchases", trying to get a name.
    Is [Pro/Staff Member Name] a FK to a table of Staff Members?

    Why not add the Staff Members table to the query , link the PK/FK fields and add the First & Last names to the query design grid?


    --------------------------------
    Suggestions about naming objects:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not use Look up FIELDS, Multi-Value fields or Calculated fields in tables.
    Do not begin object names with a number.


    Object names include: fields, tables, queries, forms, reports

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 4 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This is my personal point of view.
    I never enter data directly into tables.
    I never use a table as the record source for a form/report. I always use queries.
    So having/creating look up FIELDS in tables is a waste of time for me.

    For [Purchases]![Pro/Staff Member Name], the data type would be Number - Long for use as a FK to the table that has "Staff Member" info.
    If I need the Staff Member name, I create a combo box control to be able to store the PK value in the proper FK field.

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

Similar Threads

  1. Replies: 8
    Last Post: 05-08-2018, 07:27 AM
  2. Replies: 3
    Last Post: 03-14-2018, 01:53 PM
  3. Replies: 6
    Last Post: 09-26-2016, 10:36 PM
  4. Replies: 7
    Last Post: 01-11-2012, 12:24 PM
  5. IIF Statement Returning #Error
    By DrDefpoints in forum Queries
    Replies: 6
    Last Post: 05-26-2011, 12:25 PM

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