Extract part of text from Access Record 1

[jpkeller55]

Does anybody know if a part of text can be extracted from a record? Using Access 2003

For example: With the following records, I only want the portion that contains the number followed by IU.

Records:
ADVATE 1070IU VIAL (BAXTER) FACTOR VIII RECOM
ADVATE 1390IU VIAL (BAXTER) FACTOR VIII RECOM
HELIXATE FS 1108IU VIAL (ZLB BEHRING) F:VIII
KOGENATE FS 1232IU VIAL w/BIOSET (BAYER)

Desired data:
1070IU
1390IU
1108IU
1232IU

Is this possible? thanks!

 

[Remou]

Is the part you want always four digits followed by IU? If so:

Mid(FieldName, InStr(FieldName, "IU") - 4, 6)

 

[jpkeller55]

sometimes the number preceding the IU is only 3 digits. In those cases there is a space in front that I suppose I could then delete.

 

[Remou]

It would also be possible to use code and Split to create an array. Each element of the array can be examined to see if it matches your criteria.

 

[jpkeller55]

I get an error message when using this code:

Mid(FieldName, InStr(FieldName, "IU") - 4, 6)

"Invalid Procedure Call

 

[jpkeller55]

The error message was a result of the fact that I had Unique Values set to Yes on the query properties. By changing it to NO, it works. Thanks Remou!

I would like to explore your array solution to this problem too, but wouldn't know where to start.

 

[Remou]

Where did you try this?

Here is some SQL:

[tt]SELECT Mid([Field1],InStr([Field1],"IU")-4,6) AS Expr1
FROM tblTable[/tt]

Note that you must change the field and table names to match your set up. You should get #Error returned if IU does not exist in the string. The result will not be correct if IU can occur elsewhere in the string.

 

[Remou]

Very roughly:

Dim rs As DAO.Recordset
Set rs=CurrentDB.Openrecordset("tblTable")
Do While Not rs.EOF
   astrField=Split(rs!Field," ")
   For i=0 To UBound(astrField)
      If Instr(astrField(i),"IU")>0 Then
         If IsNumeric(Left(astrField(i),Len(astrField(i))-2)) Then
              Debug.Print astrField(i)
          End If
      End If     
   Next
   rs.MoveNext
Loop

 

[jpkeller55]

I ran the script you first supplied to me in an update query to update a field in the table with the 6 characters. It worked fine. First time I was doing a select query with Unique Values set to Yes and that gave me the error message. I will try your code out too.

thanks much for your help!