How write number values to table exactly as I see them displayed on a form

Posted on 2014-08-12
Medium Priority
Last Modified: 2014-08-13
I have a number field on a form that is displayed, for example, as "63.0" because of the following afterupdate event code:

Me.txtNI_MIN = Format([txtNI_MIN], "0" & Left(".", [txtNI_SIG_FIG]) & String([txtNI_SIG_FIG], "0"))
Me.txtNI_MIN.Format = "0" & Left(".", [txtNI_SIG_FIG]) & String([txtNI_SIG_FIG], "0")

Open in new window

But I want the value in the table to be the same ("63.0").  I can't seem to make that happen in the table. I have the field properties in the table set as:

Field Size = Double
Format = (blank)
Decimal Places = Auto
Question by:SteveL13
LVL 59
ID: 40255520

  You'd have to write it as a text field, but really that's a bad idea.  Much better to format as when/where needed.

 Sorts won't work correctly.  For example:


will come out as:


 Unless every value is saved in the exact same format, you will have issues.

LVL 31

Expert Comment

ID: 40256112
In a database, tables are used to store data. You need not do any processing or formatting in tables.
But if it is a must you may add a calculated field just for that purpose as text field "Jim Dettman's comment". Use the original field for sorting.

Author Comment

ID: 40256323
I think I will use the text method.  These will never be sorted.  But if I ever needed to use them as numbers does this do the trick?


NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

LVL 59

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 40256381
Yes, but why not do the opposite?   When you need them as a string, just use format() (which is built-in in many places) or CStr().

 I guess a better question is how these will be used?   This is not typically one something does.

 When designing databases and storing data, one of the things you do is use appropriate data types and the reasons for doing so are many.   That's why different data types exist at all.     If there was no problems in doing so, then database design could be simplified by having only one data type.

 Besides the sorting, here's another way you can get tripped up by storing a number in a string:

 if "1.01"< "-9" then
    ' Do something
   ' Do Something else
End If

  What will get executed is the "Do something"

 If these were stored as numbers:

 if 1.01 < -9 then
    ' Do something
   ' Do Something else
End If

  Then it will work as it should; "Do Something else" will be executed.

  Really Steve, this is not something you normally want to do.  It almost always gets you into trouble in one form or another down the road.   It's extremely rare that you'd want to store a number as a string.

LVL 59
ID: 40256398
In fact I'm sitting here trying to think of a good example, and I only can come up with one, and that's when I was handling start/stop times.

User wanted the ability to enter minutes as either minutes (01.50 being one hour and fifty minutes) or in parts of an hour (01.50 being one hour and 30 minutes).

That's the only time when I've had to work with a number as a string in thirty years of development.

LVL 52

Expert Comment

by:Gustav Brock
ID: 40256550
>  But if I ever needed to use them as numbers ...

Then you would store them as numbers and - as already advised - format them as strings when needed.


Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
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…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

579 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