parse a string

eHigh · Feb 19, 2004

The field/string below represents regular work hours for the week:

"Su12pm-6pm M10am-9pm T10am-9pm W10am-9pm TH10am-9pm F10am-6pm Sa10am-6pm"

I would like to convert the field/string above into the field/string below:

"Su 12-6; M-Th 10-9; FS 10-6"

Any ideas on how I might accomplish this?

thanks.

LNBruno · Feb 19, 2004

Well, for a start:

Code:

sOut = Replace(sIn, &quot;pm&quot;, &quot;&quot;)
sOut = Replace(sOut, &quot;am&quot;, &quot;&quot;)

Still playing with the rest of it...

< M!ke >

LNBruno · Feb 19, 2004

Not quite there yet (and this is WAY ugly), but might give you some ideas...

Code:

Private Sub Command0_Click()
Dim sIn As String
Dim sOut As String
Dim sSu As String
Dim sM As String
Dim sT As String
Dim sW As String
Dim sTh As String
Dim sF As String
Dim sSa As String
Dim i As Integer

sIn = &quot;Su12pm-6pm M10am-9pm T10am-9pm W10am-9pm TH10am-9pm F10am-6pm Sa10am-6pm&quot;
sOut = Replace(sIn, &quot;pm&quot;, &quot;&quot;)
sOut = Replace(sOut, &quot;am&quot;, &quot;&quot;)
sOut = Replace(sOut, &quot; &quot;, &quot;; &quot;)

i = InStr(sOut, &quot;;&quot;)
sSu = Left(sOut, i)
sOut = Mid(sOut, i + 2)
i = InStr(sOut, &quot;;&quot;)
sM = Left(sOut, i)
sOut = Mid(sOut, i + 2)
i = InStr(sOut, &quot;;&quot;)
sT = Left(sOut, i)
sOut = Mid(sOut, i + 2)
i = InStr(sOut, &quot;;&quot;)
sW = Left(sOut, i)
sOut = Mid(sOut, i + 2)
i = InStr(sOut, &quot;;&quot;)
sTh = Left(sOut, i)
sOut = Mid(sOut, i + 2)
i = InStr(sOut, &quot;;&quot;)
sF = Left(sOut, i)
sOut = Mid(sOut, i + 2)
sSa = Left(sOut, i) & &quot;;&quot;
Debug.Print sSu, sM, sT, sW, sTh, sF, sSa

End Sub

< M!ke >

SkipVought · Feb 19, 2004

here's another stab...
[tt]
Function DateStr(s)
Dim t(2, 7)
For Each a In Split(s)
If LCase(a) Like "su*" Then
t(0, 0) = Left(a, 2)
t(1, 0) = Right(a, Len(a) - 2)
ElseIf LCase(a) Like "m*" Then
t(0, 1) = Left(a, 1)
t(1, 1) = Right(a, Len(a) - 1)
ElseIf LCase(a) Like "th*" Then
t(0, 2) = Left(a, 2)
t(1, 2) = Right(a, Len(a) - 2)
ElseIf LCase(a) Like "w*" Then
t(0, 3) = Left(a, 1)
t(1, 3) = Right(a, Len(a) - 1)
ElseIf LCase(a) Like "t*" Then
t(0, 4) = Left(a, 1)
t(1, 4) = Right(a, Len(a) - 1)
ElseIf LCase(a) Like "f*" Then
t(0, 5) = Left(a, 1)
t(1, 5) = Right(a, Len(a) - 1)
ElseIf LCase(a) Like "sa*" Then
t(0, 6) = Left(a, 2)
t(1, 6) = Right(a, Len(a) - 2)
End If
Next
For i = 0 To 6
If i > 0 Then
If t(1, i - 1) <> t(1, i) Then
If Right(DateStr, Len(t(0, i - 1))) = t(0, i - 1) Then
DateStr = DateStr & t(1, i - 1) & "; " & t(0, i)
Else
DateStr = DateStr & "-" & t(0, i - 1) & t(1, i - 1) & "; " & t(0, i)
End If
End If
Else
DateStr = t(0, i)
End If
Next
i = 6
If Right(DateStr, Len(t(0, i))) = t(0, i) Then
DateStr = DateStr & t(1, i)
Else
DateStr = DateStr & "-" & t(0, i) & t(1, i)
End If
DateStr = Replace(DateStr, "pm", "&quot

DateStr = Replace(DateStr, "am", "&quot

End Function
[/tt]

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

SkipVought · Feb 19, 2004

here's a bit more streamline...
[tt]
Function DateStr(s)
Dim t(2, 7)
i = 0
For Each a In Split(s)
If LCase(a) Like "su*" Or LCase(a) Like "th*" Or LCase(a) Like "sa*" Then
t(0, i) = Left(a, 2)
t(1, i) = Right(a, Len(a) - 2)
Else
t(0, i) = Left(a, 1)
t(1, i) = Right(a, Len(a) - 1)
End If
i = i + 1
Next
For i = 0 To 6
If i > 0 Then
If t(1, i - 1) <> t(1, i) Then
If Right(DateStr, Len(t(0, i - 1))) = t(0, i - 1) Then
DateStr = DateStr & t(1, i - 1) & "; " & t(0, i)
Else
DateStr = DateStr & "-" & t(0, i - 1) & t(1, i - 1) & "; " & t(0, i)
End If
End If
Else
DateStr = t(0, i)
End If
Next
i = 6
If Right(DateStr, Len(t(0, i))) = t(0, i) Then
DateStr = DateStr & t(1, i)
Else
DateStr = DateStr & "-" & t(0, i) & t(1, i)
End If
DateStr = Replace(DateStr, "pm", "&quot

DateStr = Replace(DateStr, "am", "&quot

End Function
[/tt]

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

eHigh · Feb 20, 2004

Thanks!!! This function works

The function above worked for data that's formatted correctly. But if I could extract the data directly from the table this would provide a much cleaner solution.

What if the data is contained in a table with fields for each day as shown below:

Sun Mon Tue Wed Thu Fri Sat
'12pm-6pm' '10am-9pm' '10am-9pm' '10am-9pm' '10am-9pm' '10am-6pm' '10am-6pm'

I would like to take the 7 fields above and combine the hours into one field as shown below:

Weekly_hours
'Su 12-6; M-Th 10-9; FS 10-6'

How might I extract the data from the table into an array to compare values?

Any assistance is very much appreciated.

SkipVought · Feb 20, 2004

eHigh,

I think that the heavy lifting has been done.

Let's see what code YOU have developed so far?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

eHigh · Feb 23, 2004

This is the code I've developed so far and also a list of about the first 20 records in the table.

Sub CombineRegHours()
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim strSQL As String
Dim sun, mon, tue, wed, thu, fri, sat As String

strSQL = "SELECT reg_Sun, reg_Mon, reg_Tue, reg_Wed, " _
& "reg_Thu, reg_Fri, reg_Sat FROM [LIB2003-Main]"

Debug.Print strSQL

Set db = CurrentDb()
Set rec = db.OpenRecordset(strSQL)

Do Until rec.EOF
sun = rec("reg_Sun&quot

mon = rec("reg_Mon&quot

tue = rec("reg_Tue&quot

wed = rec("reg_Wed&quot

thu = rec("reg_Thu&quot

fri = rec("reg_Fri&quot

sat = rec("reg_Sat&quot

Debug.Print sun & " " & mon & " " & tue & " " & wed & " " _
& thu & " " & fri & " " & sat

rec.MoveNext
Loop
rec.Close
End Sub

N/A|N/A|N/A|N/A|N/A|N/A|N/A
N/A|9-Sep|9-Sep|9-Sep|9-Sep|5-Sep|5-Sep
N/A|NA|NA|NA|NA|NA|N/A
N/A|N/A|N/A|N/A|N/A|N/A|N/A
NA|NA|NA|NA|NA|NA|NA
N/A|N/A|N/A|N/A|N/A|N/A|N/A
N/A|9|9|9|9|9|4
N/A|N/A|10-6:30|2:30-5:30|10-6:30|N/A|N/A
N/A |N/A |N/A |N/A |N/A |N/A |N/A
n/a|1-Sep|5-Jan|1-Sep|5-Jan|1-Sep|n/a
Closed|9-Oct|6-Oct|6-Oct|6-Oct|6-Oct|Closed
n/a|9:30-8|9:30-5:30|9:30-5:30|9:30-8|9:30-5:30|2-Oct
N/A|7-Sep|7-Sep|7-Sep|7-Sep|9-5:30|4-Sep
N/A|3:30 p.m.- 7:30 p.m.|9:00-6:00|9:00-6:00|9:00-6:00|9:00-6:00|9:00-12:00 Noon
N/A|1:00-5:00|1:00-7:00|1:00-5:00|1:00-7:00|1:00-5:00|10:00-2:00
closed|7-Sep|7-Sep|7-Sep|6-Sep|6-Sep|4-Sep
na|na|na|na|na|na|na
NA|NA|NA|NA|NA|NA|NA
NA|NA|NA|Closed|12:30 - 5:00|NA|NA
CLOSED|6-Oct|6-Oct|CLOSED|6-Oct|6-Oct|1-Sep
N/A|N/A|N/A|N/A|N/A|N/A|10:00-2:00
NA|NA|NA|NA|NA|NA|NA
N/A|9-Oct|9-Oct|9-Oct|9-Oct|6-Oct|6-Oct
N/A|9-5:30|N/A|N/A|N/A|N/A|Closed
NA|NA|NA|NA|NA|NA|NA
N/A|N/A|N/A|N/A|N/A|N/A|N/A
CLOSED|CLOSED|8-Nov|8-Nov|6-Sep|5-Aug|1-Sep
NA|NA|NA|NA|NA|NA|NA
N/A|6-Dec|N/A|6-Dec|N/A|6-Dec|1-Sep
NA|NA|NA|NA|NA|NA|NA
N/A|N/A|N/A|N/A|N/A|N/A|N/A
N/A|7-Sep|6-Sep|6-Sep|6-Jan|6-Jan|2-Sep
n/a|8-Dec|8-Oct|6-Oct|8-Dec|6-Oct|4-Oct
NA|6-Sep|8-Oct|6-Sep|8-Oct|6-Sep|6-Sep
NA|NA|NA|NA|NA|NA|NA
N/A|7-Sep|7-Sep|7-Sep|7-Sep|7-Sep|N/A
n/a|9:30-8|9:30-5:30|9:30-5:30|9:30-8|9:30-5:30|9:30-1

How can I modify the sub procedure above to get this last record in the format below?

MTh 9:30-8; TWF 9:30-5:30; S 9:30-1

In the query design view I use the 'IIf statement below to clean up the data, but would prefer to put all code in the sub procedure or function.

rsun: IIf([reg_Sun]='N/A' Or [reg_Sun]='NA' Or [reg_Sun]='n/a' Or [reg_Sun]='na' Or [reg_Sun]='Closed' Or [reg_Sun]='Not Open' Or [reg_Sun]='not open',' ',[reg_Sun])

Thanks in advance for any assistance.

eHigh · Feb 23, 2004

Modified code to this point but still struggling.

Sub CombineRegHours2()
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim strSQL As String
Dim dateStr As String
Dim regHour(7)
Dim i As Integer

strSQL = "SELECT reg_Sun, reg_Mon, reg_Tue, reg_Wed, " _
& "reg_Thu, reg_Fri, reg_Sat FROM [LIB2003-Main]"

Set db = CurrentDb()
Set rec = db.OpenRecordset(strSQL)

Do Until rec.EOF
regHour(0) = rec("reg_Sun&quot

regHour(1) = rec("reg_Mon&quot

regHour(2) = rec("reg_Tue&quot

regHour(3) = rec("reg_Wed&quot

regHour(4) = rec("reg_Thu&quot

regHour(5) = rec("reg_Fri&quot

regHour(6) = rec("reg_Sat&quot

For i = 0 To 6
If i > 0 Then
If regHour(i - 1) <> regHour(i) Then
dateStr = dateStr & regHour(i - 1) & "; " & regHour(i)
Else
dateStr = dateStr & "-" & regHour(i - 1) & regHour(i)
End If
Else
dateStr = regHour(i)
End If
Next
i = 6
If regHour(i) = regHour(i) Then
dateStr = dateStr & regHour(i)
Else
dateStr = dateStr & "-" & regHour(i)
End If

Debug.Print dateStr

rec.MoveNext
Loop
rec.Close
End Sub

SkipVought · Feb 24, 2004

"How can I modify the sub procedure above to get this last record in the format below?"

Simple!

You put together a string that looks like
[tt]
"Su12pm-6pm M10am-9pm T10am-9pm W10am-9pm TH10am-9pm F10am-6pm Sa10am-6pm"
[/tt]
then run that string thru the function.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

SkipVought · Feb 24, 2004

eHigh,

All of the sudden, you slip in a significant change in the implied specification.

NOW you want the TIME SPANS grouped rather than DAYS grouped.

Is this really what you want?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

eHigh · Feb 24, 2004

Thanks Skip for all your help!!!

I finally got my brain to focus on mult-dimensional arrays and with some modifications to the dateStr functions (i.e., empty strings, comma delimited strings, etc.) the data is looking pretty good now.

Thanks again!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

parse a string

eHigh

Programmer

LNBruno

Programmer

LNBruno

Programmer

SkipVought

Programmer

SkipVought

Programmer

eHigh

Programmer

SkipVought

Programmer

eHigh

Programmer

eHigh

Programmer

SkipVought

Programmer

SkipVought

Programmer

eHigh

Programmer

Similar threads

Part and Inventory Search

Sponsor