Custom Field Format to display Refraction Data

Posted on 2011-10-12
Last Modified: 2012-05-12
I am needing to display a number field on a form that is used in medical eye care.  The number corresponds to the spherical correction in a pair of glasses.  The number needs to display a + sign if the value is positive and a negative sign if the value is negative.  If the value is zero it needs to default to the word "plano" and if the field is null display nothing.  It should also be fixed at 2 decimal points.

The following field format works perfectly in the database table.  However, the form is based on a query and the formating does not display in the form.

I have tried using the same custom formating as an input mask on the form but that doesn't work.


The database we are using is Acess 2007 front end and MS SQL Server 2008 back end.  Any way to do this?
Question by:isurgyn
    LVL 75

    Accepted Solution

    "I have tried using the same custom formating as an input mask on the form"
    Seems you would want to use the Format property, not the Input Mask ...


    Author Closing Comment

    OK that was just too easy.  I guess I was confused by the Format property having a drop down and assumed those were the only General, Currency, Percent, Euro etc.

    So to solve the problem just type the format that I had configured into the Format property of the field on the form and it works great.

    Thanks as million.
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    You are welcome. Seemed like the right answer :-)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    761 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

    8 Experts available now in Live!

    Get 1:1 Help Now