1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Microsoft Access Query Help Nested IIf Statement

Discussion in 'Business Applications' started by rocknroller404, Aug 1, 2018.

Thread Status:
Not open for further replies.
Advertisement
  1. rocknroller404

    rocknroller404 Thread Starter

    Joined:
    Aug 1, 2018
    Messages:
    9
    Hi there,


    I'm creating a nested Iff statement in a query to return the corresponding cost for each value:

    0-500 = 6,48
    501-750 = 4,32
    751+ = 2,16

    2,16 displays correctly for values over 751.
    4,32 displays correctly for values 501-750
    6,48 displays correctly for values 0-500. HOWEVER! Amongst the 6,48 output, there are values that should have been caught in the over 751 value region (2,16), for example numbers such as '1170', etc. Below is my statement:

    Cost: IIf([Value]>="0" And [Value]<="500","6,48",IIf([Value]>="501" And [Value]<="750","4,32",IIf([Value]>="751","2,16",Null)))

    If anyone could help in any way would be very appreciated, I am very unsure as to why some of the values that should be in the 2,16 (over 751) are in the 6,48. Thank you!
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    From your description, your values do not actually appear to be Numeric Values.
    Are they by any chance Text, as you have them enclosed in quotes.
     
    rocknroller404 likes this.
  3. rocknroller404

    rocknroller404 Thread Starter

    Joined:
    Aug 1, 2018
    Messages:
    9
    Hi there! Thank you for you response.

    These are indeed text values, the dataset was imported and it defaulted to that. If I change the type to numeric, then run the query, in the Cost output field, every field is filled in with #Error. When I remove the quotation marks and run it again, it throws an error about about the commas.

    I'm not sure as to why this is happening
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What version of Access are you using?
    Can you copy some of the data to Excel for me to import in to an Access database to test t?
     
    rocknroller404 likes this.
  5. rocknroller404

    rocknroller404 Thread Starter

    Joined:
    Aug 1, 2018
    Messages:
    9
    I think it's 2007-2013! Attached is the values to be pulled into the query
     

    Attached Files:

  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, the values are preceded by an Apostrophe.
    You could run an update query and remove the apostrophe.
     
    rocknroller404 likes this.
  7. rocknroller404

    rocknroller404 Thread Starter

    Joined:
    Aug 1, 2018
    Messages:
    9
    What do you mean? I don't see where the apostrophe is
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    On the excel sheet you supplied the cells have a caution against them saying that they are text or there is an apostrophe in front of the value. You cannot see it.
    So in the first Cell click on the Editing line and place the cursor at the front of the value and press the delete button.
    The value will now appewar as a number formatted as Currency or 2 decimal points.
     
    rocknroller404 likes this.
  9. rocknroller404

    rocknroller404 Thread Starter

    Joined:
    Aug 1, 2018
    Messages:
    9
    Thank you, you're awesome! Absolute lifesaver, it fixed it
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1213834

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice