Format Properties for Number That Contain Powers

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I have numbers that I am trying to enter into access to use in conversion
tables and sometimes the numbers maybe like 1.036368 x 10^-4 or 6.24196 x
10^19 or even 0.404687.... I am trying to determine what would be the best
property for a field that contains such a varying array of number schemes.
Any suggestions would be helpful.
 
Paul said:
I have numbers that I am trying to enter into access to use in conversion
tables and sometimes the numbers maybe like 1.036368 x 10^-4 or 6.24196 x
10^19 or even 0.404687.... I am trying to determine what would be the best
property for a field that contains such a varying array of number schemes.
Any suggestions would be helpful.
Click to expand...

My personal suggestion is to decide on a convention and stick to it, and
perhaps even write a VBA routine to act like the built-in Format() but
coercing users' input into a consistent format for saving. It really
doesn't matter what the convention is but as long it's consistent,
that's the only thing that counts.

To provide an example suppose we decided to require that all numbers be
notated in this manner:

X.YYY x 10 ^ Z

and the user input in a say, 0.123 in the textbox. The VBA routine
should then convert it into this:

1.230 x 10 ^ -1


Or maybe you may prefer to not use the scientific notation and in such
case, when the user input 1.23 x 10 ^ -3, it should be converted into
0.00123. The point being it should be consistent throughtout.

HTH.
 
Hi Paul,

You will need to use a Number data type, with field size of Single. If you
click into the Field Size property, and hit F1, you should see
context-sensitive Help:

Single Stores numbers from –3.402823E38 to –1.401298E–45 for negative values
and from 1.401298E–45 to 3.402823E38 for positive values.

You can set a Scientific format, but that's going to apply for all records.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Tom said:
Hi Paul,

You will need to use a Number data type, with field size of Single. If you
click into the Field Size property, and hit F1, you should see
context-sensitive Help:

Single Stores numbers from –3.402823E38 to –1.401298E–45 for negative values
and from 1.401298E–45 to 3.402823E38 for positive values.

You can set a Scientific format, but that's going to apply for all records.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
Click to expand...

Just so Paul knows - Single or Double is certainly a great & efficient
way to store a large range of numbers, but if accuracy is essential, one
should be careful with such data types due to rounding errors inherent
in those types. However, I seem to recall for Single, it's good up to 7
digits right, and 15 digits for double (??), and if Paul takes the
proper precautions in comparing & managing those types, it can be a good
way without having to get into more complicated representation such as
Currency, Decimal, combined fields of numeric data to represent integer
& fractional parts.
 
Back
Top