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