Split Function?

[mickmullen]

I have a string of text on a form (in a text box)


Example: "Includes: Room 1, Room 2, Room 3,"


I am looking to extract the Rooms to turn into a SQL statement


"Room 1" OR "Room 2" OR Room 3"


I need to loose the "Includes:" and the commas, including the comma at the end.


I see the Split Function appears to separate the Variants per comma location.


How do I lose the "Includes"?


ultimately, after they are split, how do I turn that into my String?

 

[theDBguy]

Hi,

You can use the Replace() function to replace any part of a text with another text including an empty string. Then, you can use Split() to get each item in an Array variable and then use Join() to put them back together with the OR operator.

Hope it helps...

 

[June7]

Will there always be comma and space between each element? Will there always be that trailing comma (which is a real pain in the neck)?

Consider:

Dim sS As String
sS = Me.textboxName
sS = Replace(sS, "Includes: ", "")
sS = Left(sS, Len(sS) - 1)
sS = "IN('" & Replace(sS, ", ", "','") & "')"

 

[MajP]

Public Sub test3()
  Debug.Print GetRooms("includes: Room 1, Room 2, Room 3")
End Sub

Public Function GetRooms(StrRooms As String) As String
   Dim i As Integer
   Dim aRooms() As String
   Dim strOut As String
   GetRooms = Replace(StrRooms, "includes:", "")
    aRooms = Split(GetRooms, ",")
    aRooms = Split(GetRooms, ",")
   For i = 0 To UBound(aRooms)
    If strOut = "" Then
      strOut = "'" & Trim(aRooms(i)) & "'"
    Else
      strOut = strOut & ", '" & Trim(aRooms(i)) & "'"
    End If
  Next i
   GetRooms = " IN (" & strOut & ")"
End Function

 IN ('Room 1', 'Room 2', 'Room 3')

 

[mickmullen]

June7- Thank you! That worked, except rooms that have 2 names are not winding up in the string


Example- "Staff Lounge 123" is not updating. However "Exam Rooms" are


EDIT: NM, I think I had something wrong in the Replace Function. Working fine now

 

[mickmullen]

Whats wrong with this:

If Me!Text206 = "All" And Me!Text208 <> "All" And Me!chkExcludeRoom = No And Me!chkExcludeMemo = Yes Then
    
    strCriteria2 = Me.Text208
    strCriteria2 = Replace(strCriteria2, "Included: ", "")
    strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 1)
    strCriteria2 = "[Estimate Data].[Memo] NOT IN('" & Replace(strCriteria2, ", ", "','") & "')"
    strCriteria = "[Estimate Data.Room Number] Like '*'"

When I Check the box on the form I get error 3075, all the code looks right until strCriteria and striCriteria2 come up, I get "AND () AND ()"


NOT IN, right? Everything is working until I added that.


Edit: Its Not NOT IN. It has something to do with the check box


Edit2- Heres all the code that works. As soon as I enter info about the check box, and actually check the box I get issues

Private Sub Check261_Click()
    Dim SQL As String
    Dim strCriteria As String
    Dim strCriteria2 As String
'Room Number = All; Memo = All; Check Box Room = No; Check Box Memo = No
    If Me!Text206 = "All" And Me!Text208 = "All" Then
    
    strCriteria = "[Estimate Data.Room Number] Like '*'"
    
    strCriteria2 = "[Estimate Data.Memo] Like '*'"

   
'Room Number = All; Memo = Selected; Check Box Room = No; Check Box Memo = No
    ElseIf Me!Text206 = "All" And Me!Text208 <> "All" Then
    
    strCriteria2 = Me.Text208
    strCriteria2 = Replace(strCriteria2, "Included: ", "")
    strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 1)
    strCriteria2 = "[Estimate Data].[Memo] IN('" & Replace(strCriteria2, ", ", "','") & "')"
    strCriteria = "[Estimate Data.Room Number] Like '*'"
       
'Room Number = Selected; Memo = All; Check Box Room = No; Check Box Memo = No
    ElseIf Me!Text206 <> "All" And Me!Text208 = "All" Then
    
    strCriteria = Me.Text206
    strCriteria = Replace(strCriteria, "Included: ", "")
    strCriteria = Left(strCriteria, Len(strCriteria) - 1)
    strCriteria = "[Estimate Data].[Room Number] IN('" & Replace(strCriteria, ", ", "','") & "')"
    strCriteria2 = "[Estimate Data.Memo] Like '*'"

'Room Number = Selected; Memo = Selected; Check Box Room = No; Check Box Memo = No
    Else
    
    If Me!Text206 <> "All" And Me!Text208 <> "all" Then
    
    strCriteria = Me.Text206
    strCriteria = Replace(strCriteria, "Included: ", "")
    strCriteria = Left(strCriteria, Len(strCriteria) - 1)
    strCriteria = "[Estimate Data].[Room Number] IN('" & Replace(strCriteria, ", ", "','") & "')"
    strCriteria2 = Me.Text208
    strCriteria2 = Replace(strCriteria2, "Included: ", "")
    strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 1)
    strCriteria2 = "[Estimate Data].[Memo] IN('" & Replace(strCriteria2, ", ", "','") & "')"
    

    
    End If
    
    End If
    
    SQL = "UPDATE [Estimate Data] " & _
        "SET Completed = True " & _
        "WHERE [Project Number] = [Forms]![Workorders1]![Project Number] AND[Class] = [Forms]![Workorders1]![cboClass] AND (" & strCriteria & ") AND (" & strCriteria2 & ");"
    DoCmd.RunSQL SQL

End Sub

 

[isladogs]

You need to use True & False for the checkboxes - NOT Yes & No

 

[mickmullen]

I changed to True/False- Still Getting same "AND () AND ()" Error

 

[June7]

Set breakpoints and step debug.

Should that last Else be ElseIf? Or eliminate the If because that condition is the only one possible after the others fail.

What happens if one or both of controls is Null?

 

[Cahyanto]

No need to parse the string, just use this sql-where clause:

SELECT RoomsTable.*
FROM RoomsTable
WHERE InStr(1,"Includes: Room 1, Room 2, Room 3,",[Rooms],1)
ORDER BY RoomsTable.Rooms

[rooms] is the name of the field that contains data: room 1, room 2, room 3 and so on ...

hopefully can help

 

[June7]

Oh, doh, should have seen that , nice catch Cahyanto. But need to compare result to a value.

WHERE InStr(1,"Includes: Room 1, Room 2, Room 3,",[Rooms],1)>0

Code can reference a control (combobox maybe) on form for dynamic input:

strCriteria = "InStr(1, " & Me.comboboxname & ", [Rooms],1)>0"

 

[Cahyanto]

Yeah... it's better June7, is that mean your problem solved?

 

[mickmullen]

I figured out the issue- when one of the excluded boxes is checked the text in the text206 or text208 will never be all. So I had to change the if statement to reflect reality.

However, I would like to ask question about the instr function you are referencing. It appears to use this, I would need to know the room numbers I'm looking for? Room numbers are different with every project we do. I will need to reference the text in text206 & text208 to build my sql string. Unless I'm missing something

 

[Cahyanto]

Hi Mickmullen...
Of course the string to be evaluated could be changes base on the conditions.

I give an example of this illustration :

dim strReservedRooms as string
strReservedRooms = Text01 & ", " & Text02 & ", " & Text03

if you want to make list of the reserved rooms :
MyComboBoxCtl.RowSource = "Select * From RoomsTable WHERE InStr(1," & strReservedRooms & ",[Rooms],1)>0"
MyComboBoxCtl.Refresh

if you want to make list of the vacant rooms
MyComboBoxCtl.RowSource = "Select * From RoomsTable WHERE InStr(1," & strReservedRooms & ",[Rooms],1)=0"
MyComboBoxCtl.Refresh

hopefully can help

 

[Cahyanto]

Sorry forgot to put quotes on before and after strReservedRooms string, correction:

MyComboBoxCtl.RowSource = "Select * From RoomsTable WHERE InStr(1, '" & strReservedRooms & "', [Rooms],1)>0"