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?

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

Thank you very much...

:D