Results 1 to 6 of 6
  1. #1
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82

    Dynamically build Select Case expressionlist

    I am tying to build an expressionlist for a Select Case statement based on values in a table. Basicly:

    SQL = "SELECT tblINF_SettlementWBS.WBS,tblINF_SettlementWBS.HasS ettlement_JNs FROM tblINF_SettlementWBS;"


    Set RST = CurrentDb.OpenRecordset(SQL)


    If RST.BOF And RST.EOF Then


    RST.Close


    Set RST = Nothing


    MsgBox "Error creating Networks"


    Else




    RST.MoveFirst


    Do Until RST.EOF


    IfRST!HasSettlement_JNs = True Then


    If IsNull(strHasSettlement) Or strHasSettlement = "" Then


    strHasSettlement = RST!WBS


    Else


    strHasSettlement= strHasSettlement & ", " & RST!WBS


    End If


    Else


    If IsNull(strNoSettlement) Or strNoSettlement = "" Then


    strNoSettlement =RST!WBS


    Else


    strNoSettlement = strNoSettlement & ", " & RST!WBS


    End If


    strNoSettlement = RST!WBS


    End If


    ' If RST!HasSettlement_JNs = True Then

    ' If IsNull(strHasSettlement) Or strHasSettlement = "" Then

    ' strHasSettlement = "'" & RST!WBS & "'"

    ' Else

    ' strHasSettlement = strHasSettlement & ", '" & RST!WBS &"'"

    ' End If

    ' Else

    ' If IsNull(strNoSettlement) Or strNoSettlement = "" Then

    ' strNoSettlement = "'" & RST!WBS & "'"

    ' Else

    ' strNoSettlement = strNoSettlement & ", '" & RST!WBS &"'"

    ' End If

    ' strNoSettlement = "'" & RST!WBS & "'"

    ' End If


    RST.MoveNext


    Loop


    RST.Close


    Set RST = Nothing





    Select Case Left(WorkPackageWBS, 5)


    Case strHasSettlement


    NewNetwork= MakeNextNetwork(J8V)


    'INSERT NEW NETWORK INTO TABLE


    DoCmd.RunSQL "INSERT INTO …...




    CasestrNoSettlement


    'CREATE 1 NETWORK


    NewNetwork = MakeNextNetwork(B1V)


    'INSERT NEW NETWORK INTO TABLE


    DoCmd.RunSQL "INSERT INTO …….




    EndSelect


    End If


    i have tried building the
    expressionlist using the comment out and non-commented out code. it looks like it is reading the "x, y, g" or
    "'x', 'y', 'g'"
    (depending on code used) instead of "x", "y", "g"

    how can i get the to be
    expressionlist
    "x", "y", "g"?

    any help is GREATLY appreciated.



  2. #2
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82
    sorry about the left justified code..... looked better on the entry form

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    without indentation or identifying spacing, your code is difficult to read and work out where one If/else starts and finishes. I've redone the first bit and enclosed in code tags (highlight code the click the # button)

    Code:
    SQL = "SELECT tblINF_SettlementWBS.WBS,tblINF_SettlementWBS.HasS ettlement_JNs FROM tblINF_SettlementWBS;"
    Set RST = CurrentDb.OpenRecordset(SQL)
    
    
    If RST.BOF And RST.EOF Then
        RST.Close
        Set RST = Nothing
        MsgBox "Error creating Networks"
    Else
        RST.MoveFirst
        Do Until RST.EOF
            
            If RST!HasSettlement_JNs = True Then
    
                If IsNull(strHasSettlement) Or strHasSettlement = "" Then
                    strHasSettlement = RST!WBS
    
                Else
                    strHasSettlement= strHasSettlement & ", " & RST!WBS
    
                End If
    
           Else
               If IsNull(strNoSettlement) Or strNoSettlement = "" Then
                   strNoSettlement =RST!WBS
    
               Else
                   strNoSettlement = strNoSettlement & ", " & RST!WBS
    
               End If
    
               strNoSettlement = RST!WBS
    
            End If
    to answer your question you need to double up on your quotes, i.e. enclose your double quotes in quotes. In the immediate window

    ?"""X""" & "," & """Y""" & "," & """Z"""
    "X","Y","Z"

    Edit: You can simplify your code considerably. Assuming strHasSettlement has been declared as type string, it cannot be null so there is no point checking for null. Similarly unless there is good reason for a particular order, add your comma first, then when finished just remove the first comma

    Code:
    SQL = "SELECT tblINF_SettlementWBS.WBS,tblINF_SettlementWBS.HasS ettlement_JNs FROM tblINF_SettlementWBS;"
    Set RST = CurrentDb.OpenRecordset(SQL)
    
    
    If RST.BOF And RST.EOF Then
       RST.Close
       Set RST = Nothing
       MsgBox "Error creating Networks"
    Else
       RST.MoveFirst
       Do Until RST.EOF
           
           If RST!HasSettlement_JNs = True Then
              strHasSettlement= ", """ & RST!WBS & """ & strHasSettlement
           
           Else
             strNoSettlement = ", """ & RST!WBS & """ & strNoSettlement
             strNoSettlement = RST!WBS 'don't know why you have this
    
           End If
           ...
           ...
    
       after loop finished
       strHasSettlement=mid(strHasSettlement,2)
       strNoSettlement=mid(strNoSettlement,2)
    

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Depends on what Left(WorkPackageWBS, 5) is, but this might work.

    Code:
    SQL = "SELECT tblINF_SettlementWBS.WBS,tblINF_SettlementWBS.HasS ettlement_JNs FROM tblINF_SettlementWBS;"
    Set RST = CurrentDb.OpenRecordset(SQL)
    If RST.BOF And RST.EOF Then
     RST.Close
     Set RST = Nothing
     MsgBox "Error creating Networks"
    Else
     RST.MoveFirst
     Do Until RST.EOF
     If RST!HasSettlement_JNs = True Then
      If IsNull(strHasSettlement) Or strHasSettlement = "" Then
       strHasSettlement = RST!WBS
      Else
       strHasSettlement= strHasSettlement & ", " & RST!WBS
      End If
     Else
      If IsNull(strNoSettlement) Or strNoSettlement = "" Then
       strNoSettlement =RST!WBS
      Else
       strNoSettlement = strNoSettlement & ", " & RST!WBS
      End If
      strNoSettlement = RST!WBS
     End If
    ' If RST!HasSettlement_JNs = True Then
    ' If IsNull(strHasSettlement) Or strHasSettlement = "" Then
    ' strHasSettlement = "'" & RST!WBS & "'"
    ' Else
    ' strHasSettlement = strHasSettlement & ", '" & RST!WBS &"'"
    ' End If
    ' Else
    ' If IsNull(strNoSettlement) Or strNoSettlement = "" Then
    ' strNoSettlement = "'" & RST!WBS & "'"
    ' Else
    ' strNoSettlement = strNoSettlement & ", '" & RST!WBS &"'"
    ' End If
    ' strNoSettlement = "'" & RST!WBS & "'"
    ' End If
     RST.MoveNext
     Loop
     RST.Close
     Set RST = Nothing
     dim L as string
     dim M as string
     dim R as string
     L = Mid(strHasSettlement,1,1)
     M = Mid(strHasSettlement,3,1)
     R = mid(strHasSettlement,5,1)
     Select Case Left(WorkPackageWBS, 5)
      Case L & "," & M & "," & R    
       NewNetwork= MakeNextNetwork(J8V)
       'INSERT NEW NETWORK INTO TABLE
       DoCmd.RunSQL "INSERT INTO …...
      Case strNoSettlement
      'CREATE 1 NETWORK
       NewNetwork = MakeNextNetwork(B1V)
       'INSERT NEW NETWORK INTO TABLE
       DoCmd.RunSQL "INSERT INTO …….
     EndSelect
    End If
    or maybe
    Code:
     dim L as string
     dim M as string
     dim R as string
     dim concat as string
     L = Mid(strHasSettlement,1,1)
     M = Mid(strHasSettlement,3,1)
     R = mid(strHasSettlement,5,1)
     concat = L & "," & M & "," & R
     Select Case Left(WorkPackageWBS, 5)
      Case concat
    

  5. #5
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82
    Thanks for the inputs but something is still not quite right. when I drop in your code
    strHasSettlement= ", """ & RST!WBS & """ & strHasSettlement



    Access adds an extra " at the end (and will not let me delete it)
    strHasSettlement= ", """ & RST!WBS & """ & strHasSettlement"


    if I change the code to:
    strHasSettlement= ", """ & RST!WBS & """" & strHasSettlement

    it appears to work. In the Immediate window:
    ?strHasSettlement
    "1.4.9", "1.3.9"

    but if I pass 1.3.9 to the Select Case statement it doesn't trigger on Case strHasSettlement

    When I float over the Case strHasSettlement the curser shows ""1.4.9", "1.3.9""

    thoughts? I know I could just hard code the items but I have a feeling that the list will change over time.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you will need to play around with it, also perhaps there is something not right about your case statement. Also might be easier to use single quotes - but depends on your code to get 'x', 'y', 'g'

    strHasSettlement= ",'" & RST!WBS & "'" & strHasSettlement

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

Similar Threads

  1. Dynamically Build Query String
    By Juan4412 in forum Programming
    Replies: 6
    Last Post: 05-24-2017, 10:11 AM
  2. Pl sql case select
    By mrmmickle1 in forum Queries
    Replies: 1
    Last Post: 11-17-2015, 11:14 PM
  3. Should I /can I use a Select Case statement?
    By Gina Maylone in forum Access
    Replies: 1
    Last Post: 12-13-2014, 12:08 PM
  4. Select case help
    By killermonkey in forum Programming
    Replies: 7
    Last Post: 10-25-2013, 05:09 PM
  5. Replies: 23
    Last Post: 07-29-2011, 04:24 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