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

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
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Steve,

  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:

6.00
30.00
04.00

will come out as:

04.00
30.00
6.00

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

Jim.
0
hnasrCommented:
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.
0
SteveL13Author Commented:
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?

=Val(textfield)

??
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
Else
   ' 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
Else
   ' 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.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

Jim.
0
Gustav BrockCIOCommented:
>  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.

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.