Picking of single data out of a string of it..

[shantigal]

hi,

I am trying to pick out the ERAP no.s out of a whole string of data. ERAP no.s are like invoice no.s. Do I Append or Make table? How do i go about doing this? I want the ERAP no. in a row next to the the description field all next to its respective string of data too....


for an example..

1)ERAP43463 STAFF TEAM LUNCH MID-YEAR REVIEW

2) 0507 SUBMITTED ITEMIZED ACCR-ERAP43159

See.. the part where the ERAP no. appears is inconsistant.. if not i could simply use excel and use text to columns. We have thousands of lines like this every week... There has to be a way to pick out just this detail and fill in the column next to the respective datastring...

Can someone please advice me on how i can go about doing this?

 

[Guus2005]

Create a function which extracts the ERAP no. Something like

Public Function GetERAP(strInput As String) As String

    Dim intStart  As Integer
    Dim intEnd    As Integer
    Dim strRetval As String
    
    strRetval = ""
    intStart = InStr(1, strInput, "ERAP")
    If intStart > 0 Then 'Found
        intEnd = InStr(intStart, strInput, " ")
        If intEnd > 0 Then
            strRetval = Mid$(strInput, intStart, intEnd - intStart)
        Else
            strRetval = Mid$(strInput, intStart)
        End If
    End If

    GetERAP = strRetval

End Function

Then you have to create a query like this

INSERT INTO Table1 (Field1, Field2) SELECT ERAPCODE, GetERAP([ERAPCODE]) FROM SomeOtherTable

Or something similar.

HTH

 

[shantigal]

Thank you very much...