How can I keep an expression from dropping perenthesis in phone number input mask?

Posted on 2008-11-11
Last Modified: 2013-11-24
I have a report using the following expression:

=IIf([HomePhone] Is Null,"",[HomePhone] & Chr(13) & Chr(10)) & [MobilePhone] & " (cell)" & IIf([SpousePhone] Is Null,"",Chr(13) & Chr(10) & [SpousePhone] & " (" & [SpouseName] & ")")

The expression does what it is supposed to do, except, I am using an input mask for the phone numbers and so the phone number is coming out without any perenthesis and dashes like it should.  For example, the phone number (111) 222-3333 is coming out on the report as 1112223333 and is unacceptable.  I have searched and have been unsuccessful in finding a solution.  Can this be resolved?

Thanks in advance for help!

Question by:defixer
    LVL 20

    Expert Comment

    If you use a mask, the parentheses and dashes are not actually stored in the database. You need to put them in yourself when you pull the information back out. You may want to set up a function that will do that, but how the function is written depends on what database application you're using. You've posted your question in the dBase zone, but the use of brackets around the field names makes it look like you may be working in Access instead. If you can post what you're working in, I (or someone else) can help you with writing an appropriate function, if you need help.


    Author Comment

    Sorry about that.  When I submitted my question I was pretty sure I selected Microsoft Access.  Guess I goofed.  You are right I'm using MS Access 2003.  Thanks for the reply.
    LVL 20

    Accepted Solution

    I don't do much programming in Access. I think my answer about masks is correct for there as well as dBase, but if you need assistance with designing a function to put parentheses and dashes into each phone number, you should post a new question in the Access zone.

    Author Comment

    Thank you.  I reposted in the Access Zone and got a fix in just a couple of days.

    Author Closing Comment

    I don't know what the policy is, but he got me pointed in the right direction and when I reposted in the Access Zone where I thought I was when I posted this question I quickly got a solution.  I would like to see him awarded the points if possible because he did try and was very courtious and helpful even though he was unable to give me a resolution a resolution.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
    The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now