Unanswered: Grab only numbers out of a text string (MS Access)
I created a program for sending out dunning letters for past due invoices. The program evaluates past due invoices and generates cover letters and accompanying invoices then merges the cover letter and invoice(s) as a single PDF. At that time the program emails the PDF to RCFax.com who then, in turn, faxes the dunning letters to the customers. How we tell RC fax whom to send the letters to is by having the fax number @rcfax.com like firstname.lastname@example.org.
My question is, is there any way to convert the text string in our ERP to show only numerical values? I have a series of "Replace" functions removing such things as parenthesis and hyphens successfully, however, I've started noticing additional characters I'd like removed. I tried creating a larger series of replace functions to remove all non-numeric characters, however this quickly became too "complex" to perform in a query. Some examples of fax number formating are:
I know ideally an ERP should catch these formating issues when fax numbers were entered, however, these fax numbers are located in the AR rep's account notes and correct formating was not a big issue until now. I keep thinking the easiest way to remove all characters should be if there was something like a Val function that returned only number values in a string, out of a text string with other characters. Anybody else had to deal with this?
The very easiest and (possibly) most efficient is an expression like:
SELECT Iif(Mid(theColumn, 1, 1) LIKE "[0-9]", Mid(theColumn, 1, 1)) & Iif(Mid(theColumn, 2, 1) LIKE "[0-9]", Mid(theColumn, 2, 1)) & Iif(Mid(theColumn, 3, 1) LIKE "[0-9]", Mid(theColumn, 3, 1))..... AS numberCol
It is easy to write (copy and paste) but not very extensible (if you do up to fifteen characters and one day get one that is 16 in length, you are stuffed).
A more flexible method is a user defined VBA function - pass in the string. Iterate through the characters, concatenating each numeric one onto the end of a variable and return that. This (may) run more slowly, but will handle any number of characters.