having probles with instr in my query

[snodrift1]

I need to be able to take a list of instructions like this one, that is in a table, and have it break out in a query so that when I go to use it in a report it will look like the second example.

1) Set the oil out so that it is at room temperature. 2) Mix the oil and the alcohol together. 3) Place oil mixture on stove and bring it up to73 degrees. 4) Pour the mixture through a cheese cloth. 5) Add 1 cup


1) Set the oil out so that it is at room temperature.
2) Mix the oil and the alcohol together.
3) Place oil mixture on stove and bring it up to73 degrees.
4) Pour the mixture through a cheese cloth.
5) Add 1 cup

I have tried different ways of using instr in my query. I can get it to start the next line at any point, but I do not seem to be able to get it to stop when it comes to the next instruction. As a result I get something like this.

3) Place oil mixture on stove and bring it up to73 degrees. 4) Pour the mixture through a cheese cloth. 5) Add 1 cup

4) Pour the mixture through a cheese cloth. 5) Add 1 cup

 

[Mile-O]

What you actually want is a table for instructions where each one occupies a record to itself.

 

[snodrift1]

I wish I could. The information is coming through and ODBC, where in the source table the individual users are only allowed to put in one long line. That's where my problem is stemming from

 

[Mile-O]

Is it always in the 1) 2), 3), number/bracket format?

 

[snodrift1]

It can be. This is somethig we just started and it could go to 1: 2: or evensomething else. But for now yes.

 

[Mile-O]

Create a new standalone module, copy the following, and paste it into the module.

Now you can use the function fDisplayMethod in your query/form/report to return an ordered list. Obviously I've not fully tested it, but I don't see it causing much problems.

Public Function fDisplayMethod(ByVal strText As String) As String

    On Error GoTo fDisplayMethod

    Dim intCounter As Integer
    Dim intIterations As Integer
    Dim strMessage As String
    
    intIterations = fCountCharactersInString(strText, ")")
    
    For intCounter = 1 To intIterations
    
        strMessage = Replace(strMessage, " " & intCounter, vbNullString)
        strMessage = strMessage & intCounter & ") " & GetPart(strText, ")", intCounter + 1) & vbCrLf
    
    Next intCounter

    fDisplayMethod = strMessage
    
Exit_fDisplayMethod:
    Exit Function
    
Err_fDisplayMethod:
    fDisplayMethod = vbNullString
    
End Function ' fDisplayMethod



Public Function fCountCharactersInString(ByVal strText As String, ByVal strCharacter As String) As Integer

    On Error GoTo Err_CountCharactersInString
    
    Dim astrCountArray() As String
    
    astrCountArray = Split(strText, strCharacter)
    
    fCountCharactersInString = UBound(astrCountArray()) - LBound(astrCountArray())
    
Exit_CountCharactersInString:
    Exit Function
    
Err_CountCharactersInString:
    CountCharactersInString = 0
    Resume Exit_CountCharactersInString
End Function ' CountCharactersInString



Public Function fGetPart(strString As String, strSep As String, 
    intPart As Integer) As String

    On Error GoTo Err_fGetPart

    Dim intFound As Integer
    Dim intNext  As Integer
   
    intFound = InStr(1, strString, strSep)
   
    If intFound > 0 Then
        If intPart = 1 Then
            fGetPart = Mid$(strString, 1, intFound - 1)
        Else 'intPart > 1
            fGetPart = fGetPart(Mid$(strString, intFound + 1), strSep, intPart - 1) 
        End If
    Else 'intFound = 0, no occurence of seperator so return complete string
        fGetPart = strString
    End If
   
Exit_fGetPart:
   Exit Function
   
Err_fGetPart:
   fGetPart = vbNullString
   Resume Exit_fGetPart
   
End Function ' fGetPart

Thanks to Guus2005 for the Recursive GetPart function.

 

[snodrift1]

I am lost with how to use that. Meanwhile I'm trying to make sense of something like this

Expr4: IIf(Len([NOTETEXT_I])>0,(Left([NOTETEXT_I],InStr([NOTETEXT_I],"4)")-1)) & " " & (Mid([NOTETEXT_I],InStr([NOTETEXT_I],"3)"))),"")

Which of course is just making a mess. If I could just get it to start at 3) and end at 4) I think I could get it to all come together.

 

[Brianwarnock]

expr4:fDisplayMethod(NOTETEXT)

having first pated the code into a module as stated by Mile-o

Brian