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!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Tough One 1

Status
Not open for further replies.

chrisaroundtown

Technical User
Joined
Jan 9, 2003
Messages
122
Location
AU
I have text in a SCHEDULE field that looks like this:

.........................................................................................................BBBBBBBBBBBBBBB....................................................................................................................................................................................LLLLLLLLLLLLLLLLLLLLLLLLLLLLLL.........................................................................................................BBBBBBBBBBBBBBB............................................................

Believe it or not but this represents a staff member's schedule. Each character reprensents one minute. A dot "." indicates the staff member is due to on the phones for that minute, a "B" is a break and "L" is lunch.

In this example there are 120 dots before the first "B", I'd like to be able to count them and put them in one field, then I'd like to count the "B"'s and put that count in another field, then of course count the next set of dots and so on.

Basically I need to be able to say where a break falls in a shift and how long it is for.

Any ideas?

Thanks
Chris
 
I created a form called Form1 with the following fields:

Text0 - Holds the "Schedule"
Text1 through Text10 - Holds the "Schedule" parts
Command1 - Contains the below code for the work:

Dim strChar As String
Dim strCurrentChar As String
Dim i As Integer
Dim intCount As Integer
Dim intField As Integer

strCurrentChar = Left(Me![Text0], 1)
intCount = 0
intField = 1
For i = 1 To Len(Me![Text0])
strChar = Mid(Me![Text0], i, 1)
If strChar = strCurrentChar Then
intCount = intCount + 1
Else
Me("Text" & intField) = strCurrentChar & " - " & CStr(intCount)
intField = intField + 1
intCount = 1
strCurrentChar = strChar
End If
Next i

I then pasted your example "Schedule" into Text0 and click the command button. Text1 shows .-105, Text2 shows B-15, Text3 shows .-180, Text4 shows L-30 and so on....should be easily modified to place the data where you like....Let me know if you need help with that.


=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com
 
Thanks Robert,

This is great stuff, I am having one problem though. The schedules usually end with dots, your code is picking up the last occurences of B's and L's and so forth but not the dots at the very end of the string. Is there something in the code which prevents it from picking up the last lot of dots?

Also I have a few strings that are just dots with no B's or L's, is there a way the code can pick this up to.

Thanks
Chris
 
Sorry i missed that....placing another occurance of:

Me("Text" & intField) = strCurrentChar & " - " & CStr(intCount)

immediately after the Next i line should do the trick....please let me know.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com
 
That's great, it works.

Now what else I'd like to do is replace certain characters in the string, but I'd like to replace a whole bunch at once and I'd like to do it from a query so VB is no good. I am using the below at the moment but this only replaces one character at a time.

SchNOAL: Replace([SCHEDULE],Chr(1),"",1)

Any ideas?

Thanks
Chris
 
This solves the problem but an idea on how to make it shorter/less complicated?

SchOutOf: Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([SCHEDULE],Chr(1),"",1),Chr(2),"",1),Chr(9),"",1),Chr(9),"",1),Chr(11),"",1),Chr(12),"",1),Chr(14),"",1),Chr(21),"",1),Chr(32),"",1)

Thanks
Chris
 
Okay, if i follow you correctly you have a string such as:

today.I,went?to>the<zoo#and^saw@the$bears

and you want to replace any of the &quot;special&quot; characters with a space in my example (I think you are trying to replace with empty string &quot;&quot;)

The below code can do it all at once. Copy all the code below into a standard module and save it as basChangeLetters. You then need only to supply the three necessary arguements and you will get what you want. In you query, it would be something like:

ConvertedValue: dhTranslate([fieldname], &quot;.,?><#^@$&quot;, &quot;&quot;)

This will replace all occurances of the first group of characters with whatever is in the second. It is a one for one translation, except at the end. So if you have:

dhTranslate(&quot;this.is,a:test&quot;, &quot;.,:&quot;, &quot; _&quot;)

you results would be:

this is_a_test

becuase the . is associated with the space, the , with the underscore, and since there is nothing defined for the :, it also gets associated with the underscore.

Let me know if you have any problems.

********* BEGIN CODE **********
Public Function dhTranslate(ByVal strIn As String, ByVal strMapIn As String, _
ByVal strMapOut As String) As String

Dim lngI As Long
Dim lngPos As Long
Dim strChar As String * 1
Dim strOut As String

'If there's no list of characters to replace, there's no point going on with the work
If Len(strMapIn) > 0 Then
'Right fill the strMapOut set
If Len(strMapOut) > 0 Then
strMapOut = Left$(strMapOut & String(Len(strMapIn), Right$(strMapOut, 1)), Len(strMapIn))
End If
For lngI = 1 To Len(strIn)
strChar = Mid$(strIn, lngI, 1)
lngPos = InStr(1, strMapIn, strChar, vbBinaryCompare)
If lngPos > 0 Then
'If strMapOut is empty, this doesn't fail, because Mid handles empty strings gracefully
strOut = strOut & Mid$(strMapOut, lngPos, 1)
Else
strOut = strOut & strChar
End If
Next lngI
End If
dhTranslate = strOut

End Function

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com
 
Robert,

You are doing well.

One other thing I need to do is return the position the first time one of these special characters is used.

For example, in the text string I gave above tell me the position of the first B, L or ;.

Thanks
Chris
 
Do you need to know which characetr was found first? Or just the position of the first special character?

So if you have &quot;Let'sBgoLto;the;park&quot;
do you need 6 or B6?

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com
 
At first I'd need to just know the position of the first special character, I might come back to you later if I need both.

Thanks
Chris
 
The code below returns the first position of any special character specified...it is used like:

?FindFirstChar(&quot;This.is:my;test^to*determine&the#results&quot;, &quot;;*&quot;)

which = 11

and

?FindFirstChar(&quot;This.is:my;test^to*determine&the#results&quot;, &quot;;*.&quot;)

which = 5

' ****************** Start Code ***********************

Public Function FindFirstChar(strString As String, strChars As String) As Integer

Dim strTemp As String
Dim intTemp As Integer
Dim intLoc As Integer
Dim i As Integer

intTemp = 32000

For i = 1 To Len(strChars)
strTemp = Mid(strChars, i, 1)
intLoc = InStr(1, strString, strTemp)
If intLoc <> 0 Then
If intLoc < intTemp Then intTemp = intLoc
End If
Next i

FindFirstChar = intTemp

End Function

' ****************** End Code *************************

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com
 
Any ideas on how I can get a query to do this rather than VB code?

Thanks
Chris
 
Nothing different to run this code in a query....

In the query builder, pick a blank column (usually the last one) and put the following into the field (the top) line:

=FindFirstChar([fieldname to check], ";*.")

Input the appropriate field name and add any more special characters you wish to search for, and that column in the query will be the position of the first character found for each record...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com
 
Thanks Robert,

Your FindFirstChar function works well, on the flip side, if I want to now find the last time these characters appear.What would you change in your FindFirstChar to make it a FindLastChar?

Thanks
Chris
 
actually, it would take a bit more work, as we would have to walk through the entire string and keep track of where the last one was...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com
 
Is there no way to run the same loop back to front. I mean starting at the last character and ending at the beginning?

Thanks
Chris
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top