Delete only 1st special character 1

[bustersports]

thread700-562429

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?

 

[PHV]

A starting point:

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 Function

And the query:
UPDATE InportBizTalkStep1 SET DELDATE = myDate(DELDATE)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bustersports]

Thanks PHV, works like a charm. Very much appreciated. I would give multiple stars if allowed.