Results 1 to 7 of 7
  1. #1
    crestonj is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    4

    Expression Builder - Report - IFF statement returning false -

    Hello, I have built an Access Database to track guest for a wedding. I built a report that resembles the floor plan of the dinning room at the venue.


    Each square box represents a table located in the dinning room.

    Each guest is assigned a table name which is contained in their record with in the database.
    I added a text area to the box and have attempted to use the Expression Builder to read the database to select and display their names on their assigned table.
    The main purpose is to provide a map so that the staff at the venue will be able to lay out the placement cards on the correct tables.

    Here is the expression i tried to use, its not throwing an error, but its always returning false.

    =IIf([Notes]='Lucca',[First Name] & ' ' & [Last Name] & ' ' & [Spouse First Name] & ' ' & [Spouse Last Name],'Empty Table')

    "NOTES' is the name of the column in the database where i store the table name.

    My query SQL Statement looks like this,

    SELECT Contacts.[First Name], Contacts.[Last Name], Contacts.[Spouse First Name], Contacts.[Spouse Last Name], Contacts.Notes
    FROM Contacts
    WHERE (((Contacts.Notes)="Lucca"));


    This is also returning zero records even though i now for a fact their is one record with the value of Lucca in the "NOTES" column


    Any help would be very much appreciated.

    I apologize for being new to this forum, and if I have posted my question in the wrong place or if I haven't provided enough detail,
    I promise to all of you to learn the proper etiquette here on this forum. And I'll make it my goal to contribute only high quality content.

    Thanks again!

    James Creston

  2. #2
    crestonj is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    4
    I figured out why the data was not retrieved from my query, although i do not understand.

    The data was set to "Long Text" in the database.

    I changed it to "Short Text" and now I see only one record retrieved from my expression.

    Any one have an idea how to retrieve all the records who's Notes contain the name Lucca?

  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
    Try

    WHERE (((Contacts.Notes) Like "*Lucca*"));
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    crestonj is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    4
    Hello, Thank you for the suggestion. The query does function correctly using "Like" or =
    The problem in when using the Expression Builder to filter the query to pass relevant data to the text box

    Thank you again for your help!

  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
    I'm not sure what that means. You can concatenate a form reference if you want.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    crestonj is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    4
    I have a report with several text boxes. Each text box represents a table in the dinning room of a venue.

    On the report inside the text box I have attempted to add the following expression to the "Control Source" on the data tab for the text box.


    =IIf([Notes]='Lucca',[First Name] & ' ' & [Last Name] & ' ' & [Spouse First Name] & ' ' & [Spouse Last Name],'Empty Table')

    This expression is always evaluating to false.

    My question is, can anyone help understand why?

  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 already did, in post 3. You need Like and wildcards, not =.
    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. Replies: 4
    Last Post: 05-03-2016, 01:51 PM
  2. Replies: 2
    Last Post: 09-16-2014, 09:00 AM
  3. Replies: 7
    Last Post: 06-28-2013, 12:15 PM
  4. Report - Expression Builder
    By AndreasPanayiotou in forum Reports
    Replies: 3
    Last Post: 09-16-2012, 01:10 AM
  5. Replies: 7
    Last Post: 01-11-2012, 12:24 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