Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question Access 2010 VBA: Multiple nested IIf then else statement

    I have a number of conditions to test for and each has its' own value if true. I can get this to work in the Query design window until I get to the very last piece then I get a message that my "function is too complex". So I am trying to write this in VBA. I have commented out the rest of the code, so that I can try to get just one piece working at a time. Any help, as always is appreciated. Thank you

    I am getting "Not coded Yet" for each and every line in the my pins field. and the first one should read "Already at level III" because Eligible_Level and Last_Level both = "III"

    Function Pins() As String

    Dim Eligible_Level As String
    Dim Last_Level As String

    If [Eligible_Level] = "III" And [Last_Level] = "III" Then
    Pins = "Already at level III"


    Else

    Pins = "Not Coded Yet"

    End If


    End Function

    ***everything below commented out***
    'IIf([Eligible_Level]="II" And [Last_Level]="I","2",


    'IIf([Eligible_Level]="III" And [Last_Level]="I","2 & 3",
    'IIf([Eligible_Level]="IV" And [Last_Level]="I","2, 3, & 4",
    'IIf([Eligible_Level]="V" And [Last_Level]="I","2, 3, 4 & 5", '
    'IIf([Eligible_Level]="III" And [Last_Level]="II","3",
    'IIf([Eligible_Level]="V" And [Last_Level]="II","3, 4 & 5",
    'IIf([Eligible_Level]="IV" And [Last_Level]="III","4",
    'IIf([Eligible_Level]="V" And [Last_Level]="IV","5", )))))))))*/



    ' ElseIf [Eligible_Level] = "II" And [Last_Level] = "II" Then
    ' Pins = "already at level II"

    ' ElseIf [Eligible_Level] = "I" And [Last_Level] = "I" Then
    ' Pins = "No pin"

    ' ElseIf IsNull([Last_Level]) And [Eligible_Level] = "I" Then
    ' Pins = "1"

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    DJ,

    Try this:
    Code:
    Public Function Pins(zEL As String, zLL As String) As String
    
      If zEL = "III" And zLL = "III" Then
        Pins = "Already at level III"
      Else
        Pins = "Not Coded Yet"
      End If
    
    End Function
    PinsQryDesign.JPG

    Attached Images Attached Images
    Last edited by RetiredGeek; 2012-09-25 at 14:33.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    RG: Thank you for the well described instructions. It worked like a charm and must of know, I would not have know what to put into my Query to call my function. Thank you very much.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    DJ,

    You're welcome.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •