Results 1 to 6 of 6
  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,922
    I would use a Select Case procedure in a standard module instead.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    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's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    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 Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    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
  •  
Other Forums: Microsoft Office Forums