replace help

[durilai]

I have a simple form with a text box and a submit button. Once submitted the value in the text box is checked against a table, if there is a match it adds it to another table.
The problem is this program is set up to allow scanning of barcodes, but the ID is for ex. A000123, and it works if I enter 000123 or 123, but the "A" gives me a "no value given for one or more required paramenters" error.
I have tried doing a simple replace:

            Dim idx As Integer
            idx = Me.txtAddPrep.Text
            idx = Replace(idx, "A", "")

That does not solve the problem. I will also post the SQL call:

            strSQL = "Select * FROM tablename WHERE id = " & idx

Any help would be greatly appreciated. If this is possible, the idx alpha is not always "A" it can be any letter, so if there is a solution to replace all letters with nothing that would be even better.
Thanks

 

[JohnH]

strSQL = "Select * FROM tablename WHERE id = " & numonly(Me.txtAddPrep.Text)

[COLOR=blue]Private Function[/COLOR] [COLOR=black]numonly([/COLOR][COLOR=blue]ByVal [/COLOR][COLOR=black]str [/COLOR][COLOR=blue]As String[/COLOR][COLOR=black]) [/COLOR][COLOR=blue]As String
    Dim [/COLOR][COLOR=black]out [/COLOR][COLOR=blue]As String
    Dim [/COLOR][COLOR=black]m [/COLOR][COLOR=blue]As [/COLOR][COLOR=black]System.Text.RegularExpressions.Match
    [/COLOR][COLOR=blue]For Each [/COLOR][COLOR=black]m [/COLOR][COLOR=blue]In [/COLOR][COLOR=black]System.Text.RegularExpressions.Regex.Matches(str, "[0-9]+")
        out += m.Value
    [/COLOR][COLOR=blue]Next
    Return [/COLOR][COLOR=black]out
[/COLOR][COLOR=blue]End Function[/COLOR]

 

[durilai]

Now, I am getting a new error:

"Data Type Mismatch in Criteria Expression"

Is that because the database field is an integer?