Weeding out wrong character in string

[bjreb]

I have a bit of code that I use to weed out letters in a string. The String must be all numbers and spaces but sometimes a letter sneaks in. Right now I just iterate the string character by character using the ascii equivalent. If it comes across a letter it closes and reports an error.
Is there a small elegant way to run this with as little code as possible?

Thanks

 

[pbaldy]

What is your process now? Perhaps it can be tweaked.

 

[vbaInet]

Sure, look into the LIKE operator.

 

[plog]

1. Use Split(http://www.techonthenet.com/access/functions/string/split.php) with a space as the delimiter to create an array of strings.

2. Loop through that array and use IsNumeric(http://www.techonthenet.com/access/functions/advanced/isnumeric.php) to make sure each element is a number.

 

[vbaInet]

If it comes across a letter it closes and reports an error.
Is there a small elegant way to run this with as little code as possible?

I'm talking about this part alone, since you already have a function that iterates each character.

 

[bjreb]

Here is my code

For iCounter = 1 To iStr
iLen = Asc(Mid(sstring, iCounter, 1))
Select Case iLen
Case Is = 32

Case Is < 48
sLetter = Chr(iLen)
swawa = Replace(swawa, sLetter, "")
iStr = iStr - 1
Case Is > 57
sLetter = Chr(iLen)
swawa = Replace(swawa, sLetter, "")
iStr = iStr - 1

End Select

Next

 

[MarkK]

You can also assign a string to an array of bytes, such that each byte is the ascii code for that character. Consider the following code . . .

Private Sub Test19384710()
    Dim chars() As Byte
    Dim var

    chars = StrConv("123 This is a test 456", vbFromUnicode)
    For Each var In chars
        Debug.Print Chr(var), Asc(var)
    Next
End Sub

 

[pbaldy]

Perhaps the function in post 7 can be modified for your use?

http://www.access-programmers.co.uk/forums/showthread.php?t=277930

 

[CJ_London]

not sure if this is covered but for identifying the miscreant values

 SELECT *
 FROM myTable
 WHERE myField Like '*[a-z]*'

which should root out all upper and lowercase letters

alternatively

 SELECT *
 FROM myTable
 WHERE isnumeric(replace(myField," ",""))=false

will identify all records that have any non numeric character other than a space