Query to delete text a field that has numbers and text

[GoingCrazy]

I'm looking for a way to delete unwanted text from a field and just show the number. The field is similar to the following:

33-12121212 switch
33-12121212-502
4bc123456 control
pressure switch

Unfortunately, there is no consistency. The part number lengths vary and not all records have text or a part number in the field. As such I can't use the Left or Right parameter because I delete numbers or add text if the parameter is too long or too short. Is there anyway to use the space between the text and numbers to act as a delimiter and delete all values after the space when it reads the space? (By the way, deleting the text manually is not an option because I am dealing with a database of thousands of entries.)

I'd appreciate any help anyone can offer.

Thanks,
GoingCrazy

 

[tlbroadbent]

Check out MichaelRed's solution in thread701-86426. Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[GoingCrazy]

Thanks, I'll give it a try.

 

[JerryDennison]

Here is a solution you can use that will strip every non-numeric character. It will work where the built-in Val() function won't.

Public Function StripNonNumeric(txtInput As String) As String

Dim intCounter As Integer
Dim txtTemp As String

For intCounter = 1 to Len(txtInput)
Select Case Mid(txtInput, intCounter, 1)

Case "0" To "9"
txtTemp = txtTemp & Mid(txtInput, intCounter, 1)
End Select

Next

StripNonNumeric = txtTemp

End Function

Enjoy!

 

[fourpastmidnight]

I also have a StripChars routine that may be more beneificial to you. In my routine, you are able to enter a string of characters, each of which will be stripped out of the target string. The code, with plenty of documentation, can be downloaded at

http://www.codeguru.com/vb/articles/2119.shtml

Check out the comments to see what others have had to say about my routines.

 

[fourpastmidnight]

Just saw the bottom part of your message that I missed the first time. Definitely check out my code, I have a SplitString routine that will be extremely helpful in getting the results you need. Again, here's the URL:

http://www.codeguru.com/vb/articles/2119.shtml

If you need any help, please let me know.