Split Function? (1 Viewer)

mickmullen

Registered User.
Local time
Today, 06:22
Joined
Oct 30, 2018
Messages
19
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?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 03:22
Joined
Oct 29, 2018
Messages
21,358
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

AWF VIP
Local time
Today, 02:22
Joined
Mar 9, 2014
Messages
5,425
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, ", ", "','") & "')"
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:22
Joined
May 21, 2018
Messages
8,463
Code:
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

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

mickmullen

Registered User.
Local time
Today, 06:22
Joined
Oct 30, 2018
Messages
19
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

Registered User.
Local time
Today, 06:22
Joined
Oct 30, 2018
Messages
19
Whats wrong with this:


Code:
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



Code:
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
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 10:22
Joined
Jan 14, 2017
Messages
18,186
You need to use True & False for the checkboxes - NOT Yes & No
 

mickmullen

Registered User.
Local time
Today, 06:22
Joined
Oct 30, 2018
Messages
19
I changed to True/False- Still Getting same "AND () AND ()" Error
 

June7

AWF VIP
Local time
Today, 02:22
Joined
Mar 9, 2014
Messages
5,425
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?
 
Last edited:

Cahyanto

New member
Local time
Today, 03:22
Joined
Nov 1, 2018
Messages
6
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

AWF VIP
Local time
Today, 02:22
Joined
Mar 9, 2014
Messages
5,425
Oh, doh, should have seen that :eek:, 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"
 
Last edited:

Cahyanto

New member
Local time
Today, 03:22
Joined
Nov 1, 2018
Messages
6
Yeah... it's better June7, is that mean your problem solved?
 

mickmullen

Registered User.
Local time
Today, 06:22
Joined
Oct 30, 2018
Messages
19
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

New member
Local time
Today, 03:22
Joined
Nov 1, 2018
Messages
6
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

New member
Local time
Today, 03:22
Joined
Nov 1, 2018
Messages
6
Sorry forgot to put quotes on before and after strReservedRooms string, correction:

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

Users who are viewing this thread

Top Bottom