Results 1 to 6 of 6

IIF function

  1. #1
    matteo66 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2019
    Posts
    2

    IIF function

    In a query I have to apply the IIf function
    I practically have to make the text appear
    AABB for values from 10 to 21
    CCCC for values from 21 to 40
    DDDD for values from 41 to 50


    EEEE for values from 51 to 60
    FFFF for values from 61 to 70
    I find myself in trouble because if I apply the code
    IIf([field name]=11; "AABB";
    n times for in single values from 10 to 51 ms access of says that the formula is too complex
    Any ideas? Thank you

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,896
    I would use a Select Case procedure in a standard module instead.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,080
    To add to RuralGuy's suggestion, have the query call a user defined function. The function would have 2 parameters, the low and high values and would return the string value to the query.

  4. #4
    matteo66 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2019
    Posts
    2
    Could you post an example? Thanks

  5. #5
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,080
    Turns out you don't need to pass hi and lo, just pass the value you want to test.

    Code:
    Public Function fcnExtract(arg As Long) As String
        Select Case arg
            Case 10 To 20
                fcnExtract = "AABB"
            Case 21 To 40
                fcnExtract = "CCCC"
    
        ...etc
    
            Case Else
                fcnExtract = "ZZZZ"  ' for unknown values
        End Select
    End Function
    

  6. #6
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    275
    Try this expression in your query: IIf([field name]<41;IIf([field name]<21;"AABB";"CCC");String(4;Chr(64+(([field name]-1)\10))))

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

Similar Threads

  1. Replies: 15
    Last Post: 10-17-2018, 09:26 PM
  2. Replies: 15
    Last Post: 05-31-2017, 02:10 PM
  3. Replies: 2
    Last Post: 02-26-2017, 11:31 AM
  4. Replies: 3
    Last Post: 03-04-2016, 10:36 AM
  5. Replies: 8
    Last Post: 01-31-2014, 01:45 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
  •  
Tech Forums: Microsoft Office Forums