bustersports
Programmer
thread700-562429
Hi,
I found this thread 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 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?