Results 1 to 6 of 6
  1. 10-27-2018, 07:12 AM #1
    CraigR
    CraigR is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    27

    Post A very very lot If ElseIf code

    I have a form that has four fields that input into a If / ElseIf code.
    the fields are Dropdown (#1) StateFDI = 100, 80, 50 or 40
    Dropdown (#2) InVeg_1 = A, B, C, D, E, F, G, G2 or MUG
    Dropdown (#3) InSlope_1 = 0, 5, 10, 15 or 20
    Field (#4) InDistance_1 = 1 to 100
    These fields input into the code to output a Bushfire Rating based on Fire Weather, Vegetation, Slope & Distance
    I have the code triggered set as an event procedure on Change for InDistance.
    I keep getting a Compile error: Block If without End If and Highlights End Sub? Any help would be REALLY Great
    I have attached a text file with the full length code as I was 50000 characters too long

    The Code, I did say it was long :-)

    Private Sub InDistance_1_Change()
    If Me.StateFDI = "40" Then
    If Me.InSlope_1 = "0" Then
    If Me.InVeg_1 = "A" Then
    If Me.InDistance_1 < 10 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 9 And Me.InDistance_1 < 13 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 12 And Me.InDistance_1 < 20 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 19 And Me.InDistance_1 < 28 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 27 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then


    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "B" Then
    If Me.InDistance_1 < 6 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 5 And Me.InDistance_1 < 9 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 8 And Me.InDistance_1 < 13 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 12 And Me.InDistance_1 < 19 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 18 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "C" Then
    If Me.InDistance_1 < 7 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 6 And Me.InDistance_1 < 9 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 8 And Me.InDistance_1 < 13 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 12 And Me.InDistance_1 < 19 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 18 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "D" Then
    If Me.InDistance_1 < 10 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 9 And Me.InDistance_1 < 13 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 12 And Me.InDistance_1 < 19 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 18 And Me.InDistance_1 < 27 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 26 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "E" Then
    If Me.InDistance_1 < 6 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 5 And Me.InDistance_1 < 8 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 7 And Me.InDistance_1 < 12 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 11 And Me.InDistance_1 < 17 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 16 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "F" Then
    If Me.InDistance_1 < 4 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 3 And Me.InDistance_1 < 5 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 4 And Me.InDistance_1 < 8 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 7 And Me.InDistance_1 < 12 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 11 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "G" Then
    If Me.InDistance_1 < 4 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 3 And Me.InDistance_1 < 5 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 4 And Me.InDistance_1 < 8 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 7 And Me.InDistance_1 < 12 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 11 And Me.InDistance_1 < 50 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 50 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "MUG" Then
    Me.BAL1 = "Low"
    End If
    If Me.InSlope_1 = "5" Then
    If Me.InVeg_1 = "A" Then
    If Me.InDistance_1 < 12 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 11 And Me.InDistance_1 < 16 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 15 And Me.InDistance_1 < 24 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 23 And Me.InDistance_1 < 34 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 33 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "B" Then
    If Me.InDistance_1 < 8 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 7 And Me.InDistance_1 < 11 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 10 And Me.InDistance_1 < 16 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 15 And Me.InDistance_1 < 23 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 22 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "C" Then
    If Me.InDistance_1 < 7 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 6 And Me.InDistance_1 < 10 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 9 And Me.InDistance_1 < 15 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 14 And Me.InDistance_1 < 22 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 21 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "D" Then
    If Me.InDistance_1 < 11 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 10 And Me.InDistance_1 < 15 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 14 And Me.InDistance_1 < 22 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 21 And Me.InDistance_1 < 31 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 30 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "E" Then
    If Me.InDistance_1 < 7 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 6 And Me.InDistance_1 < 9 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 8 And Me.InDistance_1 < 13 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 12 And Me.InDistance_1 < 20 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > "BAL-19" And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "F" Then
    If Me.InDistance_1 < 5 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 4 And Me.InDistance_1 < 7 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 6 And Me.InDistance_1 < 10 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 9 And Me.InDistance_1 < 15 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 14 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "G" Then
    If Me.InDistance_1 < 4 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 3 And Me.InDistance_1 < 6 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 5 And Me.InDistance_1 < 9 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 8 And Me.InDistance_1 < 14 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 13 And Me.InDistance_1 < 50 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 50 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "MUG" Then
    Me.BAL1 = "Low"
    End If
    If Me.InSlope_1 = "10" Then
    If Me.InVeg_1 = "A" Then
    If Me.InDistance_1 < 15 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 14 And Me.InDistance_1 < 20 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 19 And Me.InDistance_1 < 29 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 28 And Me.InDistance_1 < 41 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 40 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "B" Then
    If Me.InDistance_1 < 9 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 8 And Me.InDistance_1 < 13 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 12 And Me.InDistance_1 < 19 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 18 And Me.InDistance_1 < 28 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 27 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "C" Then
    If Me.InDistance_1 < 8 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 7 And Me.InDistance_1 < 11 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 10 And Me.InDistance_1 < 17 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 16 And Me.InDistance_1 < 25 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 24 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "D" Then
    If Me.InDistance_1 < 12 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 11 And Me.InDistance_1 < 17 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 16 And Me.InDistance_1 < 24 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 23 And Me.InDistance_1 < 35 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 34 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "E" Then
    If Me.InDistance_1 < 7 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 6 And Me.InDistance_1 < 10 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 9 And Me.InDistance_1 < 15 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 14 And Me.InDistance_1 < 23 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 22 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "F" Then
    If Me.InDistance_1 < 6 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 5 And Me.InDistance_1 < 8 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 7 And Me.InDistance_1 < 13 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 12 And Me.InDistance_1 < 19 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 18 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "G" Then
    If Me.InDistance_1 < 5 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 4 And Me.InDistance_1 < 7 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 6 And Me.InDistance_1 < 11 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 10 And Me.InDistance_1 < 16 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 15 And Me.InDistance_1 < 50 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 50 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "MUG" Then
    Me.BAL1 = "Low"
    End If
    If Me.InSlope_1 = "15" Then
    If Me.InVeg_1 = "A" Then
    If Me.InDistance_1 < 19 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 18 And Me.InDistance_1 < 25 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 24 And Me.InDistance_1 < 36 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 35 And Me.InDistance_1 < 49 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 48 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "B" Then
    If Me.InDistance_1 < 12 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 11 And Me.InDistance_1 < 16 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 15 And Me.InDistance_1 < 24 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 23 And Me.InDistance_1 < 35 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 34 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "C" Then
    If Me.InDistance_1 < 9 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 8 And Me.InDistance_1 < 13 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 12 And Me.InDistance_1 < 19 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 18 And Me.InDistance_1 < 28 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 27 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "D" Then
    If Me.InDistance_1 < 14 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 13 And Me.InDistance_1 < 19 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 18 And Me.InDistance_1 < 28 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 27 And Me.InDistance_1 < 39 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 38 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "E" Then
    If Me.InDistance_1 < 8 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 7 And Me.InDistance_1 < 11 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 10 And Me.InDistance_1 < 18 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 17 And Me.InDistance_1 < 26 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 25 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "F" Then
    If Me.InDistance_1 < 8 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 7 And Me.InDistance_1 < 11 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 10 And Me.InDistance_1 < 16 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 15 And Me.InDistance_1 < 24 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 23 And Me.InDistance_1 < 100 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 100 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "G" Then
    If Me.InDistance_1 < 6 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 5 And Me.InDistance_1 < 8 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 7 And Me.InDistance_1 < 13 Then
    Me.BAL1 = "BAL-29"
    ElseIf Me.InDistance_1 > 12 And Me.InDistance_1 < 19 Then
    Me.BAL1 = "BAL-19"
    ElseIf Me.InDistance_1 > 18 And Me.InDistance_1 < 50 Then
    Me.BAL1 = "BAL-12.5"
    ElseIf Me.InDistance_1 > 50 Then
    Me.BAL1 = "Low"
    End If
    ElseIf Me.InVeg_1 = "MUG" Then
    Me.BAL1 = "Low"
    End If

    End Sub
    Attached Files Attached Files
    Reply With Quote Reply With Quote
  2. 10-27-2018, 07:42 AM #2
    isladogs's Avatar
    isladogs
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,992
    Please edit your code to indent each section like this to make it more legible ...

    Code:
    Private Sub InDistance_1_Change()
If Me.StateFDI = "40" Then
    If Me.InSlope_1 = "0" Then
    If Me.InVeg_1 = "A" Then
    If Me.InDistance_1 < 10 Then
    Me.BAL1 = "FZ"
    ElseIf Me.InDistance_1 > 9 And Me.InDistance_1 < 13 Then
    Me.BAL1 = "BAL-40"
    ElseIf Me.InDistance_1 > 12 And Me.InDistance_1 < 20 Then
    Me.BAL1 = "BAL-29

    ....
    This will probably help you identify the problem(s) yourself.
    If not, repost the3 indented code using the code tag button (#)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer
    Reply With Quote Reply With Quote
  3. 10-27-2018, 07:45 AM #3
    CJ_London
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,459
    if you are going to post a large amount of code, please use the code tags to preserve indenting to make the code more readable

    suggest use case statements rather than elseif

    better still, put the outcomes in a table and you can use a query or dlookup - then only need a couple of lines of code. using this as an example

    Code:
    If Me.InDistance_1 < 10 Then
               Me.BAL1 = "FZ"
            ElseIf Me.InDistance_1 > 9 And Me.InDistance_1 < 13 Then
               Me.BAL1 = "BAL-40"
            ElseIf Me.InDistance_1 > 12 And Me.InDistance_1 < 20 Then
               Me.BAL1 = "BAL-29"
            ElseIf Me.InDistance_1 > 19 And Me.InDistance_1 < 28 Then
               Me.BAL1 = "BAL-19"
            ElseIf Me.InDistance_1 > 27 And Me.InDistance_1 < 100 Then
               Me.BAL1 = "BAL-12.5"
            ElseIf Me.InDistance_1 > 100 Then
               Me.BAL1 = "Low"
            End If
    a table storing

    tblOutcomes
    DFrom...DTo...Result
    10.……...12.....BAL-40
    13...…….19.....BAL-29
    etc

    you may need to include other fields for your other if levels - StateFDI etc

    you could use

    dlookup("Result","tblOutComes",Me.InDistance_1 & " BETWEEN DFrom AND DTo")
    Reply With Quote Reply With Quote
  4. 10-27-2018, 10:50 AM #4
    pbaldy's Avatar
    pbaldy
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,530
    Was the file with fully-indented code added later, or did everybody miss it?

    I would put the data in a table Ajax suggested, but one way to find your mistake is to take a copy of the code and begin deleting small, complete blocks of code until you have it down to the point where you see the problem. Other educational thoughts relating to a block:

    Code:
                If Me.InDistance_1 < 10 Then
               Me.BAL1 = "FZ"
            ElseIf Me.InDistance_1 > 9 And Me.InDistance_1 < 13 Then
               Me.BAL1 = "BAL-40"
            ElseIf Me.InDistance_1 > 12 And Me.InDistance_1 < 20 Then
               Me.BAL1 = "BAL-29"
            ElseIf Me.InDistance_1 > 19 And Me.InDistance_1 < 28 Then
               Me.BAL1 = "BAL-19"
            ElseIf Me.InDistance_1 > 27 And Me.InDistance_1 < 100 Then
               Me.BAL1 = "BAL-12.5"
            ElseIf Me.InDistance_1 > 100 Then
               Me.BAL1 = "Low"
            End If
    There is no need for the ">" portion of each test. The code will drop out of the If/ElseIf block when a test is met, so you can simply test for <10, <13, <20, etc. You don't need the last test, you can simply have an Else. If a value failed all the previous tests it must logically be 100 or greater. And by the way, your code would actually miss a value of 100; you test for < and >.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com
    Reply With Quote Reply With Quote
  5. 10-27-2018, 12:46 PM #5
    CJ_London
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,459
    probably missed it

    but doesn't change the advice
    Reply With Quote Reply With Quote
  6. 10-29-2018, 10:55 AM #6
    Peter M
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    That is a lot of nested If/Then conditions! I admit I didn't read through them each, but why not use a lookup-table joined on each of the IF criteria with a filter on distance?
    e.g.

    Reference Table with the following Columns:
    StateFDI
    InSlope
    InVeg
    Distance_Min
    Distance_Max
    Bal_Value

    Join your source table to the reference table with
    Inner Join on State FDI, InSlope, InVeg
    and filter
    InDistance > Distance_Min
    InDistance <= Distance_Max

    (Depending on how you want the boundaries, move the "=" to either the Min or Max condition.)
    if max(Bal_Value)=min(Bal_Value) then
    InDistance_1=max(Bal_Value)
    else
    msgbox "Error, more than one Bal_Value found."
    end if
    Reply With Quote Reply With Quote
Please reply to this thread with any new information or opinions.
« Previous Thread | Next Thread »

Similar Threads

  1. If's with Elseif's
    By yeah in forum Programming
    Replies: 14
    Last Post: 05-17-2018, 07:07 AM
  2. Help with If...Then...ElseIf
    By willmafingerdo in forum Programming
    Replies: 2
    Last Post: 09-23-2016, 08:15 AM
  3. If then Elseif then....
    By Thompyt in forum Programming
    Replies: 3
    Last Post: 10-27-2014, 01:31 PM
  4. VBA code for Running Multiple Queries in Access 2010 using If elseif statement
    By Hans in forum Programming
    Replies: 5
    Last Post: 03-03-2013, 07:18 AM
  5. Easy one, If then ElseIf
    By Bruce in forum Forms
    Replies: 4
    Last Post: 12-01-2011, 12:44 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
  •  

Forum Rules

Other Forums: Microsoft Office Forums