Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: #Error in output query

  1. #1
    New Member
    Join Date
    Nov 2016
    Posts
    33
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default #Error in output query

    Hello,
    I have this formula in access - that converts different formatted phone numbers into a numeric field - because some fields are blank it give me an #Error message. I tried to run the query and append it separately to a table (with a right join) - excluding Nulls, etc. But I cant seem to get rid of the #Error . and I dont want to completely exclude the records that dont have phone numbers I just need them to show up as Null fields


    Any insight is appreciated!!

    Heres the formula that I use:
    Replace(Replace(Replace(Replace([Data-Opportunities TS-BB-2].[Phone],')',''),'(',''),'-',''),' ','')

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    48,938
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    9 Thread(s)

    Default Re: #Error in output query

    Have you tried using the Nz function?
    Maybe something like:
    Code:
    Replace(Replace(Replace(Replace(Nz([Data-Opportunities TS-BB-2].[Phone],""),')',''),'(',''),'-',''),' ','')
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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