Deleting extra characters in a field. 1

[Guest_imported]

I imported info from another application (stock number and prices), and in the price column, there are for many records several prices seperated by "|", for example:

1346.00|16464.00|654665.53

I only need the first price in the column and there are many thousands of records. How can I delete the "|" and all the info after?

 

[rhicks]

Copy this function to a new module, then use it in an Update Query to change the values stored in your table. This will take the value stored, then place it with the value that is to the left of the first "|". I hope this is what you wanted.

Note that the result is a string value, you may want to convert the result to currency.

Public Function fGetItem(strEntry As Variant) As String
Dim strTemp As String
Dim x As Integer
If IsNull(strEntry) Then Exit Function
For x = 1 To Len(strEntry)
If Mid$(strEntry, x, 1) = "|" Then
Exit For
Else
strTemp = strTemp & Mid$(strEntry, x, 1)
End If
Next x
fGetItem = strTemp
End Function

HTH s-)
RDH
Ricky Hicks
rdhicks@mindspring.com