Page 1 of 3 123 LastLast
Results 1 to 15 of 44

IIF function checking Text problem

  1. #1
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21

    IIF function checking Text problem

    Can anyone tell me why the following IIF statement wouldn't work?



    IIF ([species] = "Beef", "True", "False")


    No matter what the species, it says false.

    Thank you, Rockpicker

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,289
    You need proper brackets.

    iif (x = Y, TRUE part, False part)

  3. #3
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    I think my brackets are correct. It's a "short text" format in the table...would that cause problems? I saw something about LEFT....is that something I need to add to this formula somehow?

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,830
    Where are you doing this calculation? In a Query, Report, etc?
    Are you sure that there aren't any extra spaces before or after the value in the field?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  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
    20,582
    Also make sure species isn't a lookup field that's actually storing a numeric value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    Quote Originally Posted by JoeM View Post
    Where are you doing this calculation? In a Query, Report, etc?
    Are you sure that there aren't any extra spaces before or after the value in the field?
    Hi JoeM I am doing this in a report. I displayed the Species above my calculation to see if there was some problem, and it shows the species as Beef but the calculation doesn't say True
    And as for spaces, is there a way to include any spaces...is that what "*" does? I put in = "*Beef*" and it still said false.
    Thanks for your help

  7. #7
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    Species is a ShortText field...I wondered if that was causing a problem. It isn't a lookup field...it's imported into the table from an excel sheet. The report that I am using it in is based on a Join query. When I look at the table it has Beef and the other species.

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,830
    Hi JoeM I am doing this in a report.
    In what section of the Report are you doing this in?

    Species is a ShortText field.
    It shouldn't matter.

    The report that I am using it in is based on a Join query.
    Why not do the calculation in the Query instead of directly on the Report? Then you can simply drag that field from the Field List box to the Report.
    I usually find that works better, and you will instantly be able to see if it is returning the correct values for all of your data.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  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
    20,582
    Quote Originally Posted by ROCKPICKER View Post
    And as for spaces, is there a way to include any spaces...is that what "*" does? I put in = "*Beef*" and it still said false.
    I think this was missed. You'd need to use Like instead of = to use wildcards:

    IIF([species] Like "*Beef*", "True", "False")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,289
    Sorry for the brackets comment, but when I first read the post I did not see the brackets????

  11. #11
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    In the summary/Footer section of the report. I'm trying to fix a report quickly as they needed it this weekend. I need to sit down and really figure out how to do it correctly but wanted to give them something that works until then. I need to do a different calculation if the species is Beef than all the others. I don't know how to do it in the query quickly.

  12. #12
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    I went in and editted it soon after posting with the correct brackets.

  13. #13
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    Quote Originally Posted by pbaldy View Post
    I think this was missed. You'd need to use Like instead of = to use wildcards:

    IIF([species] Like "*Beef*", "True", "False")
    I tried this and it still says False. Right above the IIf statement, I displayed the species to see if I was missing something and it displays Beef.

  14. #14
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,582
    Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,225
    This is not an aggregate calc and really doesn't belong in Report footer. It calculates based on last record loaded. Try moving into Detail section.

    However, the other textbox is apparently showing value from first record loaded.

    I have tested with my report and observe these behaviors.

    Editing report RecordSource query should be just as simple as calc in textbox.
    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.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. The problem with the function sum()
    By end in forum Access
    Replies: 2
    Last Post: 01-31-2016, 03:41 PM
  2. Error Checking in a text box
    By Paul H in forum Forms
    Replies: 2
    Last Post: 07-23-2015, 01:02 PM
  3. Error 0 problem with error checking
    By SemiAuto40 in forum Programming
    Replies: 4
    Last Post: 12-07-2011, 12:21 PM
  4. Problem with IIF function
    By Hulk in forum Forms
    Replies: 3
    Last Post: 03-20-2011, 12:59 PM
  5. Problem checking for Null Value of a Textbox.
    By SIGMA248 in forum Programming
    Replies: 3
    Last Post: 08-09-2010, 08:56 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
  •  
Tech Forums: Microsoft Office Forums