Results 1 to 15 of 15
  1. #1
    stoic_bird is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    8

    Make if function shorter

    Hello everybody



    I entered this if function in a form in a Text Box, I put it in data , control source.

    this is the if function:

    =IIf([Text93]=0,0,
    IIf([Text93]=1,2,
    IIf([Text93]=2,5,
    IIf([Text93]=3,7,
    IIf([Text93]=4,10,
    IIf([Text93]=5,12,
    IIf([Text93]=6,15,
    IIf([Text93]=7,17,
    IIf([Text93]=8,20,
    IIf([Text93]=9,22,
    IIf([Text93]=10,25,
    IIf([Text93]=11,27,
    IIf([Text93]=12,30,
    IIf([Text93]=13,32,
    IIf([Text93]=14,35,
    IIf([Text93]=15,37,
    IIf([Text93]=16,40,
    IIf([Text93]=17,42,
    IIf([Text93]=18,45,
    IIf([Text93]=19,47,
    IIf([Text93]=20,50,
    IIf([Text93]=21,52,
    IIf([Text93]=22,55,
    IIf([Text93]=23,57,
    IIf([Text93]=24,60,
    IIf([Text93]=25,62,
    IIf([Text93]=26,65,
    IIf([Text93]=27,67,
    IIf([Text93]=28,70,
    IIf([Text93]=29,72,
    IIf([Text93]=30,75)))))))))))

    this function did not work, an error message say (The expression you entered is too complex.)

    are there a solution for that or how to make that function shorter?


    thank you

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    use the choose function - it starts from 1 so add 1 to text93 - and recommend give your controls meaningful names

    =choose([Text93]+1,0,2,5,7....etc)

    don't know what this is for but you might be better storing the values in a table and using dlookup

  3. #3
    stoic_bird is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    8
    let me explain the goal of this function.,
    IIf([Text93]=1,2,
    1 means one dozen of product
    2 it means two pieces discount (free) if customer purchased one dozen.

    IIf([Text93]=2,5,
    in this the discount will be five pieces if this customer purchased 2 dozen from that product, and continue like this to the end of function.

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    There are probably a half dozen ways to do it.

    in addition to Ajax's suggestions another one is a select case statement

    Code:
    private Function SomeFunctionName() as integer   ' or string, long, variant depending on your datatype
    
    select case me.Text93
    
    case 0
         SomeFunctionName= 0
    case 1
         SomeFunctionName=2
    case 2
         SomeFunctionName=5
    case 3
         SomeFunctionName=7
    case 4
         SomeFunctionName=10
    
    'add all the cases
    
    case else
    SomeFunctionName=""
    
    end select
    
    end function
    Edit: given your explanation and the likelyhood that the numbers could change over time, a table may be your best bet.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    just so I'm clear

    1 means one dozen of product
    2 it means two pieces discount (free) if customer purchased one dozen.

    means they get 12 but pay for 10

    or does it mean they get 14 but pay for 12?



  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    if using vba then another alternative is to use an array


    Code:
    function getDiscount(d as integer) as integer
    const a="0,2,5,7.....etc"
    
        getdiscount=split(a,",")(d)
    
    end function
    and in your control

    =getDiscount([Text93])

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    or you can just use a formula in your control since there appears to be an arithmetic progression

    =([text93]*2)+[text93]\2

  8. #8
    stoic_bird is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    8
    Ajax

    means they get 12 but pay for 10

  9. #9
    stoic_bird is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    8
    I didn't get it how to do it.

  10. #10
    stoic_bird is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    8
    Quote Originally Posted by Ajax View Post
    or you can just use a formula in your control since there appears to be an arithmetic progression

    =([text93]*2)+[text93]\2
    it works great but there is a mistake in some figures.
    when text93 value is 1 dozen the discount become 2.5 which is wrong, it suppose to be 2 !?.

  11. #11
    stoic_bird is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    8
    Quote Originally Posted by moke123 View Post
    There are probably a half dozen ways to do it.

    in addition to Ajax's suggestions another one is a select case statement

    Code:
    private Function SomeFunctionName() as integer   ' or string, long, variant depending on your datatype
    
    select case me.Text93
    
    case 0
         SomeFunctionName= 0
    case 1
         SomeFunctionName=2
    case 2
         SomeFunctionName=5
    case 3
         SomeFunctionName=7
    case 4
         SomeFunctionName=10
    
    'add all the cases
    
    case else
    SomeFunctionName=""
    
    end select
    
    end function
    Edit: given your explanation and the likelyhood that the numbers could change over time, a table may be your best bet.
    Thank you for answering, please where I put that code.

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I'd be using the table method.
    Then when you change to buy 12 get 3 free, easy to implement.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    it works great but there is a mistake in some figures.
    when text93 value is 1 dozen the discount become 2.5 which is wrong, it suppose to be 2 !?.
    helps if you post the code you actually used, not the code I provided - which you did not use

    my code
    ?(1*2)+1\2
    2

    but you have used
    ?(1*2)+1/2
    2.5

    you have used /, I used \

  14. #14
    stoic_bird is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    8
    Quote Originally Posted by Ajax View Post
    helps if you post the code you actually used, not the code I provided - which you did not use

    my code
    ?(1*2)+1\2
    2

    but you have used
    ?(1*2)+1/2
    2.5

    you have used /, I used \
    Many thanks to you , you really make my day.
    thanks for all the people here who reply and give solution. Thank you❤

  15. #15
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    =Int([text93]*2.5) does the job as well.

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

Similar Threads

  1. Replies: 2
    Last Post: 02-04-2017, 06:00 PM
  2. Replies: 25
    Last Post: 01-31-2017, 05:36 PM
  3. Replies: 9
    Last Post: 03-18-2014, 07:02 PM
  4. Ideas on shorter code for cascading updates
    By usmcgrunt in forum Programming
    Replies: 3
    Last Post: 12-03-2010, 12:17 PM
  5. How to make use of Function Keys (F1, F2 etc)
    By rommelgenlight in forum Access
    Replies: 1
    Last Post: 05-15-2009, 02:51 PM

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