Delete only 1st special character
Delete only 1st special character
(OP)
thread700-562429: delete only letters from a field with numbers and letters
Hi,
I found this thread http://www.tek-tips.com/viewthread.cfm?qid=562429 and it helped me out somewhat. I used the PHV recommended code listed below.
Public Function StripNonNumerics(myVar)
Dim s As String, i As Long, x As String
If Trim(myVar & "") <> "" Then
s = ""
For i = 1 To Len(myVar)
x = Mid(myVar, i, 1)
If x >= "0" And x <= "9" Then s = s & x
Next i
StripNonNumerics = s
End If
End Function
My problem is, I only want to delete the 1st char. Below is what the data I am receiving looks like vs. what it needs to be. It is being imported from Excel, with many users rom multiple countries outside of the company entering data with high turnover, so it is very difficult to get consistency.
Field is DELDATE
Contents - can be blank or date. However it is being received sometimes with a "'" (usually an apostrophe but could be a space or other character). This seems to be happening if the user deletes the date that they entered, but just my guess at this point. Either way, it bombs when importing the data, even tried importing it as a text field and converting to date.
What the code PHV suggested in the earlier post is removing all characters in the field. My query reads as -
UPDATE InportBizTalkStep1 SET InportBizTalkStep1.DELDATE = StripNonNumerics("'"); Note that I put the ("'") in an attempt to delete the apostrophe
Desired end state would be a blank field if no date entered or a date if one exists.
Any suggestions on how to get to that point?
Hi,
I found this thread http://www.tek-tips.com/viewthread.cfm?qid=562429 and it helped me out somewhat. I used the PHV recommended code listed below.
Public Function StripNonNumerics(myVar)
Dim s As String, i As Long, x As String
If Trim(myVar & "") <> "" Then
s = ""
For i = 1 To Len(myVar)
x = Mid(myVar, i, 1)
If x >= "0" And x <= "9" Then s = s & x
Next i
StripNonNumerics = s
End If
End Function
My problem is, I only want to delete the 1st char. Below is what the data I am receiving looks like vs. what it needs to be. It is being imported from Excel, with many users rom multiple countries outside of the company entering data with high turnover, so it is very difficult to get consistency.
Field is DELDATE
Contents - can be blank or date. However it is being received sometimes with a "'" (usually an apostrophe but could be a space or other character). This seems to be happening if the user deletes the date that they entered, but just my guess at this point. Either way, it bombs when importing the data, even tried importing it as a text field and converting to date.
What the code PHV suggested in the earlier post is removing all characters in the field. My query reads as -
UPDATE InportBizTalkStep1 SET InportBizTalkStep1.DELDATE = StripNonNumerics("'"); Note that I put the ("'") in an attempt to delete the apostrophe
Desired end state would be a blank field if no date entered or a date if one exists.
Any suggestions on how to get to that point?
Talk To Other Members
RE: Delete only 1st special character
CODE
Public Function myDate(myVar) Dim s As String, x As String If Trim(myVar & "") <> "" Then s = myVar x = Left(s, 1) If x < "0" Or x > "9" Then s = Mid(s, 2) If IsDate(s) Then myDate = s End If End FunctionUPDATE InportBizTalkStep1 SET DELDATE = myDate(DELDATE)
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Delete only 1st special character