Results 1 to 4 of 4
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    My IIF statement is returning incorrect answers.


    Can anyone tell me (after looking at the picture) why my if statement isn't working as expected? I'm using: Match: IIf([Quantity]=[Weight],"Match","No Match")Click image for larger version. 

Name:	Untitled.png 
Views:	7 
Size:	3.7 KB 
ID:	33066


    Backstory: I am making a recipe system in which there is 1 table that houses the main recipe and another that house the mixtures that go into it. Say you have 100kg of ingredient 1. If you put 100kg into mixture 3 the query will show that the [Weight] is 100kg and the [Quantity] from table 1 is also 100kg.

    I've checked that there are no rounding errors and I'm also getting this with scenario 20 and 20.000 "No Match"

    It's boggling me...

    Here's another snapshot with 2 different queries using the same formula.Click image for larger version. 

Name:	Untitled.png 
Views:	7 
Size:	26.2 KB 
ID:	33067

    Upon further troubleshooting, I found that retyping the entries on table 1 solved the problem. I'd still like to know what the issue is though.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My best guess is that it is how the "numbers" are being represented in the computer.
    Here is a forum thread that discusses a similar situation. I hope it is helpful.

  3. #3
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    I figured it out. By using the field [Weight] instead of [SumOfWeight] I was fetching the wrong value for my query. Say I have a total mix of 100kg and I've mixed 25kg 4x. While the query was displaying 100kg & 100kg, the IIF statement was looking at 25kg to 100kg which was wrong. Thanks for being my soundboard and I hope this helps someone in the future.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Glad you have it sorted out. It may be possible to reduce the number of forms involved, based on your graphic that shows several "possibly similar" forms. But you know your set up better than readers.

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

Similar Threads

  1. Replies: 6
    Last Post: 09-26-2016, 10:36 PM
  2. Replies: 2
    Last Post: 09-16-2014, 09:00 AM
  3. Sum query returning incorrect answer
    By chawes40 in forum Queries
    Replies: 1
    Last Post: 12-11-2012, 03:52 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

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