How can I delete a “-“character in text data?

[MikeFL]

I receive a data text file weekly that contains thousands of part numbers with a " – "character between either the 2nd character position (example: 2W-2605) or in the 3rd character position (example: 114-5447 ) that I need to remove before I can use them in my Access 8.0 (Office 97 Pro program).

I need to make them look like this when the " - " has been removed. 2W2605 and 1145447 .

Is there anyway I can program Access to automatically correct this problem?

 

[lynchg]

If there is always just one '-' character in the string you can do this directly to the field within a query:

Left(SerialNum,Instr(SerialNum,"-") - 1) & Mid(SerialNum,Instr(SerialNum,"-") + 1)

In this way you concatenate everything preceeding the dash with everything that follows the dash. If there can be more than one dash then you would have to create a loop and step through each character in the serial number and eliminate the dashes in a similar fashion to what I do above.

 

[TheAceMan1]

How are ya MikeFL . . . .

Use the following function as an example:

Function NewText(YourText As String) As String
   Dim Idx as integer
   Dim Lft As String
   Dim Rht As String

   Idx=InStr(1,YourText,"-")
   Lft=Left(YourText,Idx-1)
   Rht=Right(YourText,Len(YourText)-Idx)
   NewText=Lft & Rht
End Function

TheAceMan

 

[Gazonice]

This should replace unwanted hyphens.

TextBox = Replace(Me.TextBox, "-", "")

Garry

 

[TonyJollans]

Hi Gazonice,

A couple of points about the Replace Function:

1. It is not available prior to A2K. The questioner has stated they are using A97.
2. It is a VBA Function, so will work as in your example, but not in-line in a Query.

I would go with lynchg's simple solution in this case - possibly putting it in an Update Query to give a one-time (per week) conversion.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Golom]

Here's a Replace function for those who don't have one
[tt]
Public Function ReplaceIt(ByVal myString As String, _
ByVal strFind As String, _
ByVal strReplaceWith As String) As String

Dim n As Integer
Dim strTemp As String

strTemp = myString
n = InStr(1, strTemp, strFind)
Do While n > 0
strTemp = Left$(strTemp, n - 1) & strReplaceWith & _
Mid$(strTemp, n + Len(strFind))
n = InStr(n + Len(strReplaceWith), strTemp, strFind)
Loop
ReplaceIt = strTemp

End Function
[/tt]

 

[MikeFL]

Thanks for your suggestions help everyone!

I will get back if I still need help!