forgot to ask you twalgrave part2

OK, I'm here.  Let me take a look at this, will come back.

OK. here's the data I used (I tested with 4 records, 2 the new way and 2 the old way so I could make sure I didn't do anything that would make the old records start failing):
data

01 jim
123.00
State**
3.00
50.00
30.00
1200.00
300.00
p012
02great
     19.00
     40.00
$***120.00
$***140.00
Jim************************
$***160.00
ted
greg
01 tim
12.00**
1.00
$***35.00
3.00
12.00
123.00
1233.00
122.00
p013
02great
23.00
1.00
34.00
$***35.00
Tim*****************************
$***36.00
OK
120 Long
$***37.00
01 jimOLD
123.00
State**
3.00
50.00
30.00
1200.00
300.00
p012
02great
     19.00
     40.00
Jim************************
ted
greg
01 timOLD
12.00**
1.00
3.00
12.00
123.00
1233.00
122.00
p013
02great
23.00
1.00
34.00
Tim*****************************
OK
120 Long




Now here's the results:
INSERT INTO Final (part1, part2, part3, part4) values ('01 jim
123.00
State**
', '3.00
50.00
30.00
1200.00
300.00
p012','02great      19.00      40.00 $***120.00 $***140.00 Jim************************','$***160.00 ted greg')

INSERT INTO Final (part1, part2, part3, part4) values ('01 tim
12.00**
1.00
$***35.00
', '3.00
12.00
123.00
1233.00
122.00
p013','02great 23.00 1.00 34.00 $***35.00 Tim*****************************','$***36.00 OK 120 Long $***37.00')

INSERT INTO Final (part1, part2, part3, part4) values ('01 jimOLD
123.00
State**
', '3.00
50.00
30.00
1200.00
300.00
p012','02great      19.00      40.00 Jim************************','ted greg')

INSERT INTO Final (part1, part2, part3, part4) values ('01 timOLD
12.00**
1.00
', '3.00
12.00
123.00
1233.00
122.00
p013','02great 23.00 1.00 34.00 Tim*****************************','OK 120 Long')


Now here's the code (modified to look for *** now and to make the $ be ignored...you can look for the comments that are '2/23/2003   to see the changes):

Private Sub cmdbegin_Click()
 
 Call ParseRecords("01")

End Sub
Private Sub ParseRecords(ByVal v_sSeparator As String)
Dim sFirstRecord As String
Dim sSecondRecord As String
Dim sCurrentRecord As String
Dim bFirstRecord As Boolean
Dim sOriginalSecondRecord As String
Dim sThirdRecord As String
Dim sFourthRecord As String
Dim lFoundLocation As Long
Dim aSplit() As String

Dim sSql As String

bFirstRecord = True
Do Until Adodc1.Recordset.EOF
   
   sCurrentRecord = Adodc1.Recordset.Fields(0)
   
   If Left$(sCurrentRecord, Len(v_sSeparator)) = v_sSeparator Then
           
       'Save off the old record if there is one
       If bFirstRecord = True Then
           bFirstRecord = False
       Else
           sFirstRecord = SplitFirstRecord5Lines(sSecondRecord, sFirstRecord)
           sOriginalSecondRecord = sSecondRecord
           'sSecondRecord = GetSecondRecord(sOriginalSecondRecord, "****")
           sSecondRecord = GetSecondRecord(sOriginalSecondRecord, "p0")
           sThirdRecord = GetThirdRecord(sOriginalSecondRecord, "p0", "***")
           sFourthRecord = GetFourthRecord(sOriginalSecondRecord, "***")
           If sThirdRecord = "" Then
              sFourthRecord = ""
           Else
           
              aSplit = Split(sThirdRecord, " ")
              sThirdRecord = aSplit(0)
              sThirdRecord = Replace$(sThirdRecord, vbCrLf, " ")
              aSplit(0) = ""
              'sFourthRecord = Trim$(Join(aSplit, " "))
              sFourthRecord = Replace$(sFourthRecord, vbCrLf, " ")
           End If
           
           sSql = "INSERT INTO Final (part1, part2, part3, part4) values ('"
           sSql = sSql & sFirstRecord & "', '" & sSecondRecord & "','" & sThirdRecord & "','" & sFourthRecord & "')"
           Call InsertRecord(sSql)
           
       End If
       'Must be the start of the new record
       sFirstRecord = sCurrentRecord
       sSecondRecord = ""
       sThirdRecord = ""
       sFirstRecord = sFirstRecord & " " & GetFirstRecordInfo("p0")
           
   Else
     
      'Must be a subsequent record so put it into sSecondRecord
      sSecondRecord = sSecondRecord & " " & sCurrentRecord
   End If

    If Not Adodc1.Recordset.EOF Then
        If Left$(Adodc1.Recordset.Fields(0), 2) = v_sSeparator Then
           'NOP
        Else
            Adodc1.Recordset.MoveNext
        End If
    End If
    If Adodc1.Recordset.EOF Then
        sFirstRecord = SplitFirstRecord5Lines(sSecondRecord, sFirstRecord)
        sOriginalSecondRecord = sSecondRecord
        sSecondRecord = GetSecondRecord(sOriginalSecondRecord, "p0")
        sThirdRecord = GetThirdRecord(sOriginalSecondRecord, "p0", "***")
        sFourthRecord = GetFourthRecord(sOriginalSecondRecord, "***")
       
        aSplit = Split(sThirdRecord, " ")
'        If sThirdRecord = "" Then
'           sFourthRecord = ""
'        Else
'            sThirdRecord = aSplit(0)
'            aSplit(0) = ""
'            'sFourthRecord = Trim$(Join(aSplit, " "))
'        End If
           If sThirdRecord = "" Then
              sFourthRecord = ""
           Else
           
              aSplit = Split(sThirdRecord, " ")
              sThirdRecord = aSplit(0)
              sThirdRecord = Replace$(sThirdRecord, vbCrLf, " ")
              aSplit(0) = ""
              'sFourthRecord = Trim$(Join(aSplit, " "))
              sFourthRecord = Replace$(sFourthRecord, vbCrLf, " ")
             
           End If
       
        'last record so save out what we have
        sSql = "INSERT INTO Final (part1, part2, part3, part4) values ('"
        sSql = sSql & sFirstRecord & "', '" & sSecondRecord & "','" & sThirdRecord & "','" & sFourthRecord & "')"
        Call InsertRecord(sSql)
       
    End If
   
Loop

End Sub

Private Sub InsertRecord(ByVal v_sSQL As String)

'Remove this if you don't want to see the message box
MsgBox v_sSQL
Debug.Print v_sSQL
Debug.Print


Exit Sub
'Now use ADO to execute the SSQL statement.
Set oConn = New ADODB.Connection
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Documents and Settings\Desktop\Misc\visual basic items\read textfile\pay.mdb; Jet OLEDB:Database Password="

oConn.Open
Set oRS = New ADODB.Recordset
Set oRS.ActiveConnection = oConn
oRS.Open v_sSQL


End Sub

Private Function GetFirstRecordInfo(ByVal v_sStringLookingFor As String) As String

Dim sFirstRecord As String

Do Until Adodc1.Recordset.EOF
    Adodc1.Recordset.MoveNext
    If Not Adodc1.Recordset.EOF Then
        If Left$(Adodc1.Recordset.Fields(0), 2) = "01" Then
            Exit Do
        Else
            If Left$(Adodc1.Recordset.Fields(0), 2) = v_sStringLookingFor Then
                Adodc1.Recordset.MovePrevious
                'sFirstRecord = sFirstRecord & " " & ADODC1.Recordset.Fields(0)
                Exit Do
            Else
                'These changed back to allow vbcrlf
                'sFirstRecord = sFirstRecord & " " & Adodc1.Recordset.Fields(0)
                sFirstRecord = sFirstRecord & vbCrLf & Adodc1.Recordset.Fields(0)
               
            End If
        End If
       
    End If
Loop

GetFirstRecordInfo = sFirstRecord

End Function

Private Function GetSecondRecord(ByVal v_sOriginalLine As String, ByVal v_sBeforeText As String) As String
 
  'This function splits up the second record to not
  'include the portions of the third record
  Dim aSplit() As String
  Dim lArrayCount  As Long
  Dim lFoundLoc As Long
 
  aSplit = Split(v_sOriginalLine, v_sBeforeText)
 
  'Return the first array element
  If UBound(aSplit) > 0 Then
    GetSecondRecord = Trim$(aSplit(0)) & v_sBeforeText
    lFoundLoc = InStr(1, aSplit(1), " ")
    If lFoundLoc > 0 Then
      GetSecondRecord = GetSecondRecord & Left$(aSplit(1), lFoundLoc - 1)
    End If
     For lArrayCount = 1 To UBound(aSplit)
     
     If aSplit(lArrayCount) = "" Then
        aSplit(lArrayCount) = v_sBeforeText
        GetSecondRecord = GetSecondRecord & Trim$(Replace$(aSplit(lArrayCount), " ", vbCrLf))
     End If
       
     Next lArrayCount

  Else
    If v_sOriginalLine = "" Then
        GetSecondRecord = v_sOriginalLine
    Else
        GetSecondRecord = Trim$(aSplit(0))
    End If
  End If
 
End Function

'2/5/2003 This is the routine that changed
Private Function GetThirdRecord(ByVal v_sOriginalLine As String, ByVal v_sSeparatorLeft As String, ByVal v_sSeparatorRight As String) As String
 
  'This function splits up the original second record to
  'include the separator and all subsequent pieces
  Dim aSplit() As String
  Dim aSplit2() As String
  Dim lArrayCount As Long
  Dim lInnerArraycount As Long
  Dim sTrimmedString As String
  Dim lFoundLoc As Long
 
  'Debug.Print "Original line is originally " & v_sOriginalLine
  lFoundLoc = InStr(1, v_sOriginalLine, v_sSeparatorLeft)
  If lFoundLoc > 0 Then
     v_sOriginalLine = Trim$(Mid$(v_sOriginalLine, lFoundLoc + Len(v_sSeparatorLeft)))
     'Debug.Print
     'Debug.Print "Original line is now " & "[" & v_sOriginalLine & "]"
     lFoundLoc = InStr(1, v_sOriginalLine, " ")
     If lFoundLoc > 0 Then
        v_sOriginalLine = Mid$(v_sOriginalLine, lFoundLoc)
     End If
  End If
 
  lFoundLoc = InStr(1, v_sOriginalLine, v_sSeparatorRight)
 
  If lFoundLoc > 0 Then
     '2/23/2003 - entire Do Loop code added
     Do
        lFoundLoc = InStr(lFoundLoc + 1, v_sOriginalLine, v_sSeparatorRight)
        If Trim$(Mid$(v_sOriginalLine, lFoundLoc - 1, 1)) = "$" Then
           'NOP
        Else
           
            Exit Do
           
        End If
     
     Loop
     lFoundLoc = InStr(lFoundLoc, v_sOriginalLine, " ")
     If lFoundLoc > 0 Then
        v_sOriginalLine = Trim$(Left$(v_sOriginalLine, lFoundLoc))
        v_sOriginalLine = Replace$(v_sOriginalLine, " ", vbCrLf)
        'Debug.Print "After Finding First LeftMost Space [" & v_sOriginalLine & "]"
        'MsgBox "Check Debug Window"
     End If
       
  End If
  GetThirdRecord = v_sOriginalLine
 
End Function

Private Function GetFourthRecord(ByVal v_sOriginalLine As String, ByVal v_sSeparator As String) As String
 
  '2/23/2003 - changed entire routine, left in the old code but commented it out.

  'This function splits up the original second record to
  'include the separator and all subsequent pieces
  Dim aSplit() As String
  Dim aSplit2() As String
  Dim lArrayCount As Long
  Dim lInnerArraycount As Long
 
 
 lFoundLoc = InStr(1, v_sOriginalLine, v_sSeparator)
 
  If lFoundLoc > 0 Then
     
     Do
        lFoundLoc = InStr(lFoundLoc + 1, v_sOriginalLine, v_sSeparator)
        If Trim$(Mid$(v_sOriginalLine, lFoundLoc - 1, 1)) = "$" Then
           'NOP
        Else
           
            v_sOriginalLine = Mid$(v_sOriginalLine, lFoundLoc)
           
            Exit Do
           
        End If
     
     Loop
 
  End If
 
  'GetFourthRecord = GetFourthRecord & Trim$(Replace$(v_sOriginalLine, " ", vbCrLf))
  aSplit = Split(v_sOriginalLine, " ")
  If Left$(aSplit(0), 3) = "***" Then
    aSplit(0) = ""
  End If
  v_sOriginalLine = Join(aSplit, " ")
  GetFourthRecord = Trim$(v_sOriginalLine)
 
 
'  aSplit = Split(v_sOriginalLine, v_sSeparator)
'
'  If InStr(1, v_sOriginalLine, v_sSeparator) > 0 Then
'     'return the  second element (everything after the separator)
'     For lArrayCount = 1 To UBound(aSplit)
'
'     If aSplit(lArrayCount) > "" Then
'        sLine = aSplit(lArrayCount)
'        aSplit2 = Split(sLine, Left$(v_sSeparator, 1))
'        sLine = Join(aSplit2, "")
'        GetFourthRecord = GetFourthRecord & Trim$(Replace$(sLine, " ", vbCrLf))
'     End If
'
'     Next lArrayCount
'
'     GetFourthRecord = Trim$(Replace$(GetFourthRecord, " ", vbCrLf))
'     If Left$(GetFourthRecord, 2) = vbCrLf Then
'        GetFourthRecord = Mid$(GetFourthRecord, 3)
'     End If
'  Else
'     GetFourthRecord = ""
'  End If
 
End Function

Private Function SplitFirstRecord5Lines(ByRef r_sSecondLine As String, ByVal v_sFirstRecord As String) As String
    Dim aSplit() As String
    Dim lSplitCount As Long
    Dim sOldSecondLine As String
    Dim sReturn As String
    Dim sSecondLine As String
   
    sOldSecondLine = r_sSecondLine
    aSplit = Split(v_sFirstRecord, vbCrLf)
    For lSplitCount = LBound(aSplit) To UBound(aSplit) - 5
        sReturn = sReturn & aSplit(lSplitCount) & vbCrLf
    Next
    For lSplitCount = UBound(aSplit) - 4 To UBound(aSplit)
        sSecondLine = sSecondLine & aSplit(lSplitCount) & vbCrLf
        'sOldSecondLine = sOldSecondLine & aSplit(lSplitCount) & vbCrLf
    Next
    sSecondLine = sSecondLine & sOldSecondLine
    r_sSecondLine = sSecondLine
   
    SplitFirstRecord5Lines = sReturn
   
End Function

That works, but we now have a reoccuring problem. Last time I stated that part3 was coming in one word at a time. You fixed that problem. Well on this rewrite the problem is back. Here is what I am talking about.

instead of part3 coming in as the record was it is doing this:

02
great      
19.00      
40.00
$***120.00
$***140.00
Jim************************

It should be
02great      19.00      40.00 $***120.00 $***140.00 Jim************************

I tried removing the vbcrlf, but nothing did it. I tried to look at the old code taht fixed this, but I couldn't figure out what was changed. Once this is fixed it is great.

This is the only re-occuring problem. I can't figure out what changed in this one.

Thanks

This is copied and pasted from my previous comment:
note: I do not see part 3 having CRLFs.  I copied this straight from my debug window and this was generated using the code listed above, so I don't know where the problem is that you are talking about.  Just for giggles, I will re-run the application and will paste the results on the next message:

INSERT INTO Final (part1, part2, part3, part4) values ('01 jim
123.00
State**
', '3.00
50.00
30.00
1200.00
300.00
p012','02great      19.00      40.00 $***120.00 $***140.00 Jim************************','$***160.00 ted greg')

INSERT INTO Final (part1, part2, part3, part4) values ('01 tim
12.00**
1.00
$***35.00
', '3.00
12.00
123.00
1233.00
122.00
p013','02great 23.00 1.00 34.00 $***35.00 Tim*****************************','$***36.00 OK 120 Long $***37.00')

INSERT INTO Final (part1, part2, part3, part4) values ('01 jimOLD
123.00
State**
', '3.00
50.00
30.00
1200.00
300.00
p012','02great      19.00      40.00 Jim************************','ted greg')

INSERT INTO Final (part1, part2, part3, part4) values ('01 timOLD
12.00**
1.00
', '3.00
12.00
123.00
1233.00
122.00
p013','02great 23.00 1.00 34.00 Tim*****************************','OK 120 Long')

Same results:
INSERT INTO Final (part1, part2, part3, part4) values ('01 jim
123.00
State**
', '3.00
50.00
30.00
1200.00
300.00
p012','02great      19.00      40.00 $***120.00 $***140.00 Jim************************','$***160.00 ted greg')

INSERT INTO Final (part1, part2, part3, part4) values ('01 tim
12.00**
1.00
$***35.00
', '3.00
12.00
123.00
1233.00
122.00
p013','02great 23.00 1.00 34.00 $***35.00 Tim*****************************','$***36.00 OK 120 Long $***37.00')

INSERT INTO Final (part1, part2, part3, part4) values ('01 jimOLD
123.00
State**
', '3.00
50.00
30.00
1200.00
300.00
p012','02great      19.00      40.00 Jim************************','ted greg')

INSERT INTO Final (part1, part2, part3, part4) values ('01 timOLD
12.00**
1.00
', '3.00
12.00
123.00
1233.00
122.00
p013','02great 23.00 1.00 34.00 Tim*****************************','OK 120 Long')

So I don't know if you're trying to copy/paste and replace functionality or you are taking the whole program as-is, but mine works just fine.

I copied the whole program you did. The part 3 is coming out like this in the field
02
great      
19.00      
40.00
$***120.00
$***140.00
Jim************************

I copied exactly what you stated. I noticed that in the message box I am getting what you are getting, but in the actual field it is coming in like the above.

Why??

I do not know the answer to that question, unfortunately.  It is the InsertRecord routine that displays the msgbox and it is the same routine that outputs it to the debug window.  Nowhere in that routine does it manipulate the SQL String, it just passes it into the database as-is.  There should be no reason there are CRLFs in the DB.

A couple of questions though:

1) How are you viewing the info in the DB?  Is it directly in the database, do you have word wrapping turned on?  Is it in a form where you have say a textbox with word-wrapping?  Is it in a report of yours where you may be inserting CRLFs?

2) Does the data you are testing with contain CRLFs in the database you are reading from itself?  Mine did not, each field did not contain a CRLF

3) What happens when you try it using my data?

I'll try using your data. No it is not in the report it is in the database table. This happened before, and you changed something to make part3 work right. I was in the code before to make it do that. Then you got frustrated with me and redid the code to make the record go into part 3 as is.

I don't know what to say because there are no CRLFs in the data currently being sent in the parts 3 and 4.  I have checked, double-checked and triple-checked and still do not see any CRLF's being inserted into p3,p4.

Sorry I wasn't feeling so good the last couple of days. I tried to reapply the code and it seems to be ok. I am sorry about that. I must have been coping an old one. Also noticed that the following code works for all records expect for this one. Why?

 If Trim$(Mid$(v_sOriginalLine, lFoundLoc - 1, 1)) = "$" Then

02  120      375   01/09/2003  17227                                                  
Hank     $****89.23        
EIGHTY-NINE AND 23/100******************
part4
***89.23
 EIGHTY-NINE AND 23/100*********************                                                            
 Hank   testing                                          
 32342

should be
part3
02   120      375   01/09/2003  17727
 Hank   $****89.23  
EIGHTY-NINE AND 23/100****************
part4
  Hank           testing  

notice part3 and four

The problem is that your data does not follow any sort of rules.  That particular record has 4 asterisks after the dollar sign.  So when we find the first set of 3 asterisks, we then find the second set of 3 asterisks one more position over.  I cannot chase around these anomolies in your data all day.  You assure me this was the final change.  It isn't and I don't even think that changing this last one will be the last change you will need to make.  I don't know if you realize this, but at this point at my current rates for employment, you have gotten over $3000 worth of free work on this single project.  If I had seen your data up-front, this task would probably have taken less than 3 hours to complete and an hour to test.  I realize this forum has it's drawbacks, but it was never intended to be a situation of "do somebody else's work for them".  How many times have I heard "just this one last change?"  I think we're going on about 6 now.

So, before I go into resolving this situation, you've got to determine if it should be resolved or if the data itself and its rules should be changed.

no, I was just asking why the line doesn't just look for a $ sign and ignore that line. I am sorry for asking that. the astersik for that line would be.
$***233.00
$****33.00
$*****3.00
$******.00
$*******.0
$********0
$*********

This would be the possible situations I believe

I think I will just try a instr line
I thought that what was done in the first place
when it sees the $ ignore that line

sorry for wasting your time.

do you think that will work correctly.

or am I going to have to play with the old lines.

The above is the worst case scenerio.

going to sound very stupid but are you saying this is the fix for the third record for all the exceptions of the asterisk. I just have to do the same thing for the fourth.

Again I just want to make sure I am clear with what you are saying.

no problem with clarifying.  Yes I believe this will fix the issue with the third record for all instances of the asterisks/dollar sign, but didn't test it with anything more than 4 asterisks due to time.  You might want to implement this first, run your tests without dumping the info into the database and make sure you test with all cases of the asterisk/dollar sign combo.  If it works, then you need to do something similar with the 4th record although it won't be exactly the same because they are handled differently.

I will do, I did notice that the 4th one brings in the remaining data like before. I can give an example if you wish. I will try many asterisk for part 3. I was trying to add the same new lines that were in the third to the four, but you are correct. It doesn't like it, what it gives me is part2 data.

I added the scenerio, seems to work. but I noticed that the loop through the case doesn't stop. I actually have to hit control break to stop the program. When I go into the data table  the data is correct for part3, but I doesn't stop the case loop. The reason I know this is before one the code was finish it closed the form. Took maybe 5 seconds, now it stays running for minutes and once I hit control break it stops on the Case statements. Why. Is there something that is needed to end the loop.

If that's the case, then you might have a situation where you do not have a space before the $ sign or the $ sign is the first item in the original part 2.
I just don't have the time to deal with this right now so I gave you the code to play around with.  I don't know what data you are using to cause this problem to occur.  Use the immediate window to display what the value of v_sOriginalLine is before you get into the Do loop and look at your data.  Then single-step through the program and see what's happening and you will find out why.

think I got it. I just have to try to get part4 to work

let me know when you are available please

thanks

I am going to keep trying with part4 but I am not getting very luck. I did get the loop to end.

post what you've got for part 3 so when I post a new solution, we don't lose it.  Still don't have time to tinker with it yet.

'2/5/2003 This is the routine that changed
Private Function GetThirdRecord(ByVal v_sOriginalLine As String, ByVal v_sSeparatorLeft As String, ByVal v_sSeparatorRight As String) As String
 
 'This function splits up the original second record to
 'include the separator and all subsequent pieces
 Dim aSplit() As String
 Dim aSplit2() As String
 Dim lArrayCount As Long
 Dim lInnerArraycount As Long
 Dim sTrimmedString As String
 Dim lFoundLoc As Long
 Dim x As Long
 Dim bFoundDollarSign As Boolean
 
 'Debug.Print "Original line is originally " & v_sOriginalLine
 lFoundLoc = InStr(1, v_sOriginalLine, v_sSeparatorLeft)
 If lFoundLoc > 0 Then
    v_sOriginalLine = Trim$(Mid$(v_sOriginalLine, lFoundLoc + Len(v_sSeparatorLeft)))
    'Debug.Print
    'Debug.Print "Original line is now " & "[" & v_sOriginalLine & "]"
    lFoundLoc = InStr(1, v_sOriginalLine, " ")
    If lFoundLoc > 0 Then
       v_sOriginalLine = Mid$(v_sOriginalLine, lFoundLoc)
    End If
 End If
 
 lFoundLoc = InStr(1, v_sOriginalLine, v_sSeparatorRight)
 
 If lFoundLoc > 0 Then
    Do
       lFoundLoc = InStr(lFoundLoc + 1, v_sOriginalLine, v_sSeparatorRight)
       bFoundDollarSign = False
       For x = lFoundLoc To 1 Step -1
           Debug.Print v_sOriginalLine
           
           Select Case Mid$(v_sOriginalLine, x, 1)
           
               Case "           "
                   Exit Do
               
               Case "$"
                   bFoundDollarSign = True
                   Exit For
               Case Else
                   Debug.Print v_sOriginalLine
           End Select
         
       Next
       
       
    Loop
    lFoundLoc = InStr(lFoundLoc, v_sOriginalLine, " ")
    If lFoundLoc > 0 Then
       v_sOriginalLine = Trim$(Left$(v_sOriginalLine, lFoundLoc))
       v_sOriginalLine = Replace$(v_sOriginalLine, " ", vbCrLf)
       'Debug.Print "After Finding First LeftMost Space [" & v_sOriginalLine & "]"
       'MsgBox "Check Debug Window"
    End If
       
 End If
 GetThirdRecord = v_sOriginalLine
 
End Function


now I had to add addition spaces in the case for the loop to stop. Also I tried placing this part to getfourthrecord
lFoundLoc = InStr(1, v_sOriginalLine, v_sSeparator)
 
 If lFoundLoc > 0 Then
    Do
       lFoundLoc = InStr(lFoundLoc + 1, v_sOriginalLine, v_sSeparator)
       bFoundDollarSign = False
       For x = lFoundLoc To 1 Step -1
           Debug.Print v_sOriginalLine
           
           Select Case Mid$(v_sOriginalLine, x, 1)
           
               Case "           "
                   Exit Do
               
               Case "$"
                   bFoundDollarSign = True
                   Exit For
               Case Else
                   Debug.Print v_sOriginalLine
           End Select
         
       Next
       
       
    Loop

but I get the part2 information. Wierd. I compared the two I was confussed of what to do with the
aSplit = Split(v_sOriginalLine, " ")
 If Left$(aSplit(0), 3) = "****" Then
   aSplit(0) = ""
 End If
 v_sOriginalLine = Join(aSplit, " ")
 GetFourthRecord = Trim$(v_sOriginalLine)

So what I am getting for part3 is
Example
part3

02   410      376   01/09/2003  17727                                                      Hank                                 $****89.23                                                                          EIGHTY-NINE AND 23/100**********************************************************
part4

***89.23                                                                          EIGHTY-NINE AND 23/100**********************************************************                                                                              Hank                                                                                                                                      testing                                                                                                                                  32342

should be
part3

02   410      376   01/09/2003  17727                                                      Hank                                 $****89.23                                                                          EIGHTY-NINE AND 23/100**********************************************************
part4

                                                      Hank                                                                                                                                      testing  

This is with just adding the getthirdrecord code you did
and the case edit I had to put in

I did test it will multi asterisk
meaning
$****89.23
$*****9.23
$******.23
etc.
Getthirdrecord works ok

Also another question I have been saving each question once finished to my pc. How long are these questions and answers stored for. If they never get erased then I can quit saving them to my pc. Saves drive space for me.

No hurry for an answer. thanks

These questions are stored forever.  You can always access your own questions by clicking on your name-link and clicking on the questions at any point.  I've seen questions from the beginning of EE.

cool. thanks

hey good news I got it. At least it is working lets say.

this is what I had to do for part4

Private Function GetFourthRecord(ByVal v_sOriginalLine As String, ByVal v_sSeparator As String) As String
 
 '2/23/2003 - changed entire routine, left in the old code but commented it out.

 'This function splits up the original second record to
 'include the separator and all subsequent pieces
 Dim aSplit() As String
 Dim aSplit2() As String
 Dim lArrayCount As Long
 Dim lInnerArraycount As Long
 
 


 lFoundLoc = InStr(1, v_sOriginalLine, v_sSeparator)
 
 If lFoundLoc > 0 Then
    Do
       lFoundLoc = InStr(lFoundLoc + 1, v_sOriginalLine, v_sSeparator)
       bFoundDollarSign = False
       For x = lFoundLoc To 1 Step -1
           Debug.Print v_sOriginalLine
           
           Select Case Mid$(v_sOriginalLine, x, 1)
           
               Case " "
                   Exit Do
               
               Case "$"
                   bFoundDollarSign = True
                   Exit For
               Case Else
                   Debug.Print v_sOriginalLine
           End Select
         
       Next
 Loop
 End If
 
 
 lFoundLoc = InStr(lFoundLoc, v_sOriginalLine, " ")
    If lFoundLoc > 0 Then
       v_sOriginalLine = (Mid$(v_sOriginalLine, lFoundLoc))
       v_sOriginalLine = Replace$(v_sOriginalLine, " ", vbCrLf)
       
    End If
       
 GetFourthRecord = (v_sOriginalLine)
 'Debug.Print GetFourthRecord
 
 'old
 
  'aSplit = Split(v_sOriginalLine, " ")
 'If Left$(aSplit(0), 3) = "****" Then
  'aSplit(0) = ""
 'End If
 'v_sOriginalLine = Join(aSplit, " ")
 'GetFourthRecord = Trim$(v_sOriginalLine)
 
 
 '  aSplit = Split(v_sOriginalLine, v_sSeparator)
'
'  If InStr(1, v_sOriginalLine, v_sSeparator) > 0 Then
'     'return the  second element (everything after the separator)
'     For lArrayCount = 1 To UBound(aSplit)
'
'     If aSplit(lArrayCount) > "" Then
'        sLine = aSplit(lArrayCount)
'        aSplit2 = Split(sLine, Left$(v_sSeparator, 1))
'        sLine = Join(aSplit2, "")
'        GetFourthRecord = GetFourthRecord & Trim$(Replace$(sLine, " ", vbCrLf))
'     End If
'
'     Next lArrayCount
'
'     GetFourthRecord = Trim$(Replace$(GetFourthRecord, " ", vbCrLf))
'     If Left$(GetFourthRecord, 2) = vbCrLf Then
'        GetFourthRecord = Mid$(GetFourthRecord, 3)
'     End If
'  Else
'     GetFourthRecord = ""
'  End If
 
End Function


Again it seems to be working if you see future problems with it please let me know if not then this is done. Thanks for your help.

also is there a way to delete a certain question making it not viewable. Either by contacting the system operators or someone else. Or even editing certain parts in a forum. I added a word that I wanted to take out. No it is not a bad one.

bman9111,
Glad to see you have it working.  I will test the code a little later today and look it over for future problems.  

As far as your other questions:
1) You can request the deletion of an entire question at the community support link at the left of the page ( https://www.experts-exchange.com/Community_Support/ ).  Remember whenever you contact Community Support to give them either the question number from the URL or the URL to the question you want modified.  Also before you request a deletion of an entire question or any refund of points, you should post in your message if any experts have already commented your intentions to get your points back and wait 72 hours for response, then post the Community Support question.  If there's no comments by experts, still post your intention to delete the question so other experts know not to answer the question, but you do not have to wait the 72 hours.  Also, the request may or may not be granted epending on how the moderator sees the situation.

2) You can request the deletion of a comment if it is vulgar, or it is in violation of the membership agreement ( https://www.experts-exchange.com/jsp/infoMemberAgreement.jsp  ) such as posts a cracking website, pornography, asks for employment, etc.  Another condition to edit a comment is if you provided a live userid or password in the comment.  These are usually the only times the moderators will modify a comment.  It doesn't hurt to ask, but the moderators do not like modifying comments because they are part of the history of the question.

I hope this helps.

I'll drop a line here when I get finished reviewing the solution.  Feel free to ask away about anything on the site operations as I do know a few things about it and am happy to help.

thanks for you help. Sorry to change the data so much, The data kept changing on my side too. Appreciate it, your a great help. I was just really confussed with the code you did. I am not longer confussed thought. I took a lot of time trying and debugging what you did. I understand much more clearly now. Thanks

thanks for your time

I am totally happy you now understand the code.  Don't hesitate to ask questions on it as I will gladly help shed some light.  Have a great day!