Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    24

    Unanswered: Combining IF Statements

    Hi,

    Sorry if this is basic but I am having trouble combining code. The following code restricts a choice of repositories to choose from dependent on the first five letters.


    Private Sub FROM_REPOSITORY_Change() ' This filters the choice of repositories dependent on what is chosen in the FROM_REPOSITORY field.

    Dim REPOSITORY As String
    If Left(FROM_REPOSITORY.Text, 5) = "GFA_D" Then
    REPOSITORY = "GFA_S"
    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_S" Then
    REPOSITORY = "GFA_U"
    Else: REPOSITORY = "GFA_P"
    End If

    TO_REPOSITORY.RowSource = "SELECT REPOSITORY_NAME FROM REPOSITORY WHERE LEFT(REPOSITORY_NAME, 5) = '" + REPOSITORY + "'"
    End Sub


    This workd fine. What I want to do now is restrict the choices further, dependent on the last two letters of the repository. So it's basically filtering the choices depending on the first five and last two letters of the repository chosen.

    I have the code below I want to add in but don't know how to combine it.

    'If Right(FROM_REPOSITORY.Text, 2) = "DC" Then
    'REPOSITORY = "DC"

    'ElseIf Right(FROM_REPOSITORY.Text, 2) = "OM" Then
    'REPOSITORY = "OM"

    'ElseIf Right(FROM_REPOSITORY.Text, 2) = "RD" Then
    'REPOSITORY = "RD"

    'ElseIf Right(FROM_REPOSITORY.Text, 2) = "EM" Then
    'REPOSITORY = "EM"
    'ElseIf Right(FROM_REPOSITORY.Text, 2) = "TP" Then
    'REPOSITORY = "TP"
    'Else: REPOSITORY = "OM"

    End If



    I presume I would also have to alter the TO_REPOSITORY.RowSource statement but again don't know how.

    Any help appreciated. It's driving me mad!

    Parma

  2. #2
    Join Date
    Nov 2003
    Posts
    40

    Re: Combining IF Statements

    Okay, I am not totally sure what you are going for here. I can help you combine the codes, but what exactly do you want the out put to be??
    Give me an example of what an input would be and then what you would want the output to be.

  3. #3
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Problem: You need to make a choice on what the REPOSITORY string will be.

    Factors: The first five chars and last two chars of FROM_REPOSITORY.

    What's missing: You need to decide on how your cartesian product of the two factors will yield data. Not all 216 (is this right, math wizards?) combinations, but what do you do when:

    Left(FROM_REPOSITORY.Text, 5) = "GFA_D" and Right(FROM_REPOSITORY.Text, 2) = "DC"?

    What if Left(FROM_REPOSITORY.Text, 5) = "GFA_D" but Right(FROM_REPOSITORY.Text, 2) = "RD"

    These decisions need to be made before you can assign REPOSITORY a value, unless there is a hierarchy, i.e., the two right chars supercede whatever the left 5 are. When these choices are made, I'm sure someone in this forum can help you.

    BTW, you don't need:

    ElseIf Right(FROM_REPOSITORY.Text, 2) = "OM" Then
    REPOSITORY = "OM"

    it is handled in the Else statement.

    Also BTW, imho, you could use a Select Case...End Select structure to make your code more readable:

    Select case Right(FROM_REPOSITORY.Text, 2)
    _Case "DC"
    __REPOSITORY = "DC"
    _Case "RD"
    __REPOSITORY = "RD"
    _Case "EM"
    __REPOSITORY = "EM"
    _Case "TP"
    __'REPOSITORY = "TP"
    _Case Else
    __'REPOSITORY = "OM"
    End Select

    Take out the __, they're just for readability.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Also, with your second set of if evaluations, that seems as though it could be written:

    REPOSITORY = Right(FROM_REPSITORY.Text, 2)

    Thereby eliminating one case/if statement all together.

  5. #5
    Join Date
    Nov 2003
    Posts
    24

    Cool

    Settled on the following, trick was to change the TO_REPOSITORY.RowSource statement to use'LIKE' instead of '=' as before. Also used wildcard * for the repository.

    Is there a neater way of code instead of all the ifs?

    Thanks for your help guys, much appreciated.



    Dim REPOSITORY As String
    If Left(FROM_REPOSITORY.Text, 5) = "GFA_D" And Right(FROM_REPOSITORY.Text, 2) = "DC" Then
    REPOSITORY = "GFA_S*DC"
    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_D" And Right(FROM_REPOSITORY.Text, 2) = "OM" Then
    REPOSITORY = "GFA_S*OM"
    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_D" And Right(FROM_REPOSITORY.Text, 2) = "RD" Then
    REPOSITORY = "GFA_S*RD"
    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_D" And Right(FROM_REPOSITORY.Text, 2) = "EM" Then
    REPOSITORY = "GFA_S*EM"
    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_D" And Right(FROM_REPOSITORY.Text, 2) = "TP" Then
    REPOSITORY = "GFA_S*TP"

    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_S" And Right(FROM_REPOSITORY.Text, 2) = "DC" Then
    REPOSITORY = "GFA_U*DC"
    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_S" And Right(FROM_REPOSITORY.Text, 2) = "OM" Then
    REPOSITORY = "GFA_U*OM"
    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_S" And Right(FROM_REPOSITORY.Text, 2) = "RD" Then
    REPOSITORY = "GFA_U*RD"
    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_S" And Right(FROM_REPOSITORY.Text, 2) = "EM" Then
    REPOSITORY = "GFA_U*EM"
    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_S" And Right(FROM_REPOSITORY.Text, 2) = "TP" Then
    REPOSITORY = "GFA_U*TP"

    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_U" And Right(FROM_REPOSITORY.Text, 2) = "DC" Then
    REPOSITORY = "GFA_P*DC"
    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_U" And Right(FROM_REPOSITORY.Text, 2) = "OM" Then
    REPOSITORY = "GFA_P*OM"
    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_U" And Right(FROM_REPOSITORY.Text, 2) = "RD" Then
    REPOSITORY = "GFA_P*RD"
    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_U" And Right(FROM_REPOSITORY.Text, 2) = "EM" Then
    REPOSITORY = "GFA_P*EM"
    ElseIf Left(FROM_REPOSITORY.Text, 5) = "GFA_U" And Right(FROM_REPOSITORY.Text, 2) = "TP" Then
    REPOSITORY = "GFA_P*TP"


    TO_REPOSITORY.RowSource = "SELECT REPOSITORY_NAME FROM REPOSITORY WHERE REPOSITORY_NAME LIKE '" + REPOSITORY + "'"

    End sub

  6. #6
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Teddy has already started you on cleaning up the code. Here's what I suggest:

    Dim REPOSITORY as String
    Select case mid(FROM_REPOSITORY.Text, 5,1)
    Case "D"
    REPOSITORY = "S"
    Case "S"
    REPOSITORY = "U"
    Case "U"
    REPOSITORY = "P"
    End Select
    REPOSITORY = "GFA_" & REPOSITORY & Chr(42) & Right(FROM_REPOSITORY.Text, 2)

    TO_REPOSITORY.RowSource = "SELECT REPOSITORY_NAME FROM REPOSITORY WHERE REPOSITORY_NAME LIKE '" + REPOSITORY + "'"

    End Sub

    Since the variance in GFA_ depends only on the fifth char of FROM_REPOSITORY, you don't need to look at all of the first five, and you append whatever Right(FROM_REPOSITORY, 2) is.

    Chr(42) is *
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

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