Results 1 to 2 of 2
  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, 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
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Joshua,

    You've posted your question on the MS Excel board. Is there an Excel question hidden in there somewhere or do you need your thread to be moved to the MS Access board?

    Cheers

Posting Permissions

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