Results 1 to 7 of 7
  1. #1
    rmoore is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Feb 2020
    Posts
    47

    Iff statement returning the wrong value

    I need a lil help with this statement. =IIf([MonthsOfLoan]=1,".10",IIf([MonthsOfLoan]=2,".20",IIf([MonthsOfLoan]>=3,".30")))

    Im using this to autofill a text box on a form. The text box is formated as "Percent".
    [MonthsOfLoan] is a combo box with a value list of 1 thru 12.

    When using the above statement the output is wrong. Example: [MonthsOfLoan] =1 there is no data in the text box. When [MonthsOfLoan]=2 the output is .10. When [MonthsOfLoan]=3 the output is .20, and when [MonthsOfLoan]=4 the output is .30.

    The autofill is returning the next value and the format is not correct in the text box.



    I need the text box to autofill with: 10% if [MonthsOfLoan]=1 or 20% if [MonthsOfLoan]=2 or 30% if [MonthsOfLoan]=3 or more.

    Any suggestions would be helpful. Thanks

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I can think of two ways that you might do this:

    1) Instead of using an expression with multiple IIf's you could use DLookup() on the table that holds the data used in the combo box.

    2) Make the combo box a Value List and use an expression in the textbox. Something like: =[Combo0].[Column](1) & "%"
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I've thought or another way as well. All three are shown in the attached db if it's of interest to you.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Code:
    = -(([MonthsOfLoan] >0) + ([MonthsOfLoan] > 1)+([MonthsOfLoan] > 2))*0.1

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The text box is formated as "Percent".
    in that case you need to return a number ".10" is not a number it is text and you can't format text. There is also a requirement that monthsofloan is numeric for your formula to work

  6. #6
    rmoore is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Feb 2020
    Posts
    47
    Thanks everyone!!!! I will use Bob Fitz suggestion

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Glad to be of help. Just out of interest, which will you opt for.

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

Similar Threads

  1. Query sum returning wrong value
    By MrBologna in forum Queries
    Replies: 9
    Last Post: 10-29-2018, 02:48 PM
  2. Replies: 2
    Last Post: 11-26-2014, 06:39 AM
  3. Forms - returning wrong value
    By Sheba in forum Forms
    Replies: 1
    Last Post: 11-12-2014, 02:02 PM
  4. Returning wrong items
    By cbende2 in forum Access
    Replies: 5
    Last Post: 08-01-2014, 07:43 AM
  5. DMax returning wrong value
    By nedbenj in forum Access
    Replies: 7
    Last Post: 10-24-2007, 10:30 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
  •  
Other Forums: Microsoft Office Forums