Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    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 9999999999@rcfax.com.

    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:

    (999) 999-9999
    (999) 999-9999 A/P
    999.999.9999
    (999) 999-9999 (PAT)

    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?

    Thanks,
    Joshua

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The very easiest and (possibly) most efficient is an expression like:

    Code:
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Thanks Pootle, it looks like that field is limited to 20 characters so I should be safe running through it that many times. That should solve the problem, thanks again! Joshua

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •