Remove a single letter from a string

[VentureFree]

I have a table with a value stored as a string. It is basically a numeric value with a single letter 'T' injected into the middle of it. Example: '1234T5678'. I'd like to select this value sans 'T' as a Long value. I can do this easily in Access itself using CLng() and Replace(), but this is going to be used in VB.net, and that doesn't seem to recognize the Replace() function. Here's what my select statement looks like in Access:

Select CLng(Replace(MyValue, 'T', '') AS MyValue FROM MyTable WHERE IsNumeric(Replace(MyValue, 'T', ''))

Is there some equivalent statement that I could use which would work in VB.net? Thanks.

 

[rcombs4]

Replace should be recognized, its the CLng that would be throwing it off.

 

[cjard]

Your question doesnt seem to make sense; youre posting in the Access area saying what you have as an Access query works, and how would it be done in VB.NET -> exactly the same! VB.NET doesnt do anything with your query other than pass it to access to carry out

If youre asking how to remove T from a string ang convert to long in vb.net:

dim myL as Long = Convert.ToInt64(myString.Replace("T", ""))

 

[VentureFree]

VB.NET doesnt do anything with your query other than pass it to access to carry out

That's the way that I understood it, but it doesn't seem to actually work that way. I run the query in Access, and I get exactly the data that I want. Here's what the query looks like in my VB code:

dbAdapter = New OdbcDataAdapter("Select CLng(Replace(MyValue, 'T', '') AS MyValue FROM MyTable WHERE IsNumeric(Replace(MyValue, 'T', ''))", MyConnection)

This is the error I get when I run it

ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Undefined function 'REPLACE' in expression.

If I run any query that doesn't include the Replace function (including ones that have the CLng function) it runs fine. Of course, that doesn't help me with this particular query, though, because I need to get rid of that 'T' in the middle of the number first.