Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Delete only 1st special character

Delete only 1st special character

Delete only 1st special character

thread700-562429: delete only letters from a field with numbers and letters


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?

RE: Delete only 1st special character

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:

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close