I need to run replace([column], [new], [old]) in a query executing on an Access 2003 DB. I know stuff I could use in SQL, but I don't have this option.
I'm trying to do a query where the alpha chars are stripped out of a column. e.g. '(111) 111-1111' becomes '1111111111'.
I can write a custom VBA function and execute the query using this, but once again, can't use these functions through JET.
I'm running a .NET web application that uses an Access 2003 db.
I'm trying to incorporate a type of search page. This page executes a query like:
SELECT * FROM [table] WHERE replace([telnumber], '-', '') LIKE '1234567890'
There are many records in the [telnumber] column that have alpha chars, for instance '(123) 123-1234'. This I need to filter out before I do the comparison.
The query, using a built in VBA function, executes in a testing environment in Access.
When I run the query from my web app, it throws an exception stating something like
Replace function not found.
Based on the sample query from your comment, I wonder if it could be "good enough" to rewrite your match pattern using wildcards to account for the possible non-digit characters?
SELECT * FROM [table] WHERE telnumber LIKE '*123*456*7890'