Scientific notation is displayed when entering data in a Number field

[Icepick]

Hello,

I have a "Currency" field in a table and it holds large numbers (on which I will need to do basic arithmetic operations at some point, so I must store them as a numbers). I have set the "Standard" format on the TextBox used to display it on a form because I want to have thousand separators; I've also set "0" for decimals as I don't need to display them.

So everything displays as I want it even with the largest number that can be stored all the decimals are showing in the text field, but when I click on the field to edit the value instead of having the complete value it is displayed as scientific notation. I would like to display the complete value when editing it instead of the scientific notation, how can I achieve this, if it is possible?

A numeric example: If a user enters 1234567891012, the value displayed is indeed 1234567891012 but if they click on the field again the displayed value while editing is 1.23456789012E+11 (and it switches back to normal notation when the focus changes to another field). I would like to show 123456789012 all the time. I know that Access is capable of it most likely because if I set the Format to "Fixed" the values are always displayed completely (no scientific notation), but unfortunately I would like the thousands separators to show and it is not possible with the "Fixed" format.

Two last details, the scientific notation while editing does not kick in unless there is more than 11 digits in the number and the field width (and/or TextBox width) are sufficient to display up to 20 digits.

How can this be done?

Thanks in advance.

 

[CJ_London]

Try changing the datatype from currency to double (unless you want to truncate to 2 decimal places). Might work

Another thing to try is to set an input mask (on the data tab).

You'll need to generate your own style but I would imagine it would be something like \9,999.99.

 

[CJ_London]

quick addendum - the data type I am referring to is in your source table, not the form.

Finally, consider changing the field type to text, setting a validation rule to only allow, 0-9,.

you can include in calculations using the val function

 

[Icepick]

Thanks a lot for your help, after testing the Double type has the same behavior. I also tried to use an Input Mask before posting here but it didn't really fit my needs, the thousands separators would show even when they were not needed while you're typing (that's part of how the Input Mask works, it's possible to hide the "_" for character positions but not the separators as far as I could see unfortunately).

I think that I'm indeed left with the Text field, but it would imply to manage the number format (for thousands separators) and the input validation quite differently as you said. I think I will stick to the Currency type for now, there are only a few cases where it goes beyond 99 billions (more than 11 digits) so I suppose the users will have to deal with scientific notation for those or erase the current content and replace it with a complete number notation.

Thanks again for your help and if you or anyone else have other ideas, they are welcome of course

 

[CJ_London]

Still have to treat it as a text field, and you can always assign to a number field after input but something else perhaps to try is to put some code in the on change event - this is fired every time a character is added.

So as the user types in the number every 4th keystroke would add a comma into the display - would look better if number is entered on the right and moves of to the left as more characters are added.

Just tried this on a text field called text0

If Len(Text0.Text) = 4 Then Text0.Text = Left(Text0.Text, 1) & "," & Mid(Text0.Text, 2)

and it works fine (set the control as right justified)

Obviously needs more work for the next commas but could be a solution

 

[RainLover]

Never set up a Field as Text if it is a Number.

In the Table select Data Type as Number.

In the General Tab choose Field Size as Long Integer, Format as Standard, Decimal Places as 0 (Zero).

Delete the text box in the Form, save the Form then create a new text box.

 

[Galaxiom]

Enter 0,000 in the Format Property of the control on the form.

Normally without the Format property, the Scientific notation cuts in at about 15 digits on Double. I don't know why it does it for you at eleven. However the above Format property will force it to stay in conventional format.

BTW The reason it changes to Scientific notation is that Double cannot handle precision beyond 15 digits so it rounds beyond this.


For more significant digits use a Decimal field type which will handle 28 significant digits.

Decimal is big though because it uses four bytes to store a number. Still way less then text.

 

[RainLover]

Enter 0,000 in the Format Property of the control on the form.

Normally without the Format property, the Scientific notation cuts in at about 15 digits on Double. I don't know why it does it for you at eleven. However the above Format property will force it to stay in conventional format.

BTW The reason it changes to Scientific notation is that Double cannot handle precision beyond 15 digits so it rounds beyond this.


For more significant digits use a Decimal field type which will handle 28 significant digits.

Decimal is big though because it uses four bytes to store a number. Still way less then text.

So Galaxiom,

What is wrong with Interger.

Unless you feel that - 2,147,483,647 to 2,147,483,647 is not big enough.

BTW Decimal uses 12 Bytes
Double uses 8 Bytes
Single used 4 Bytes.

Not as though space is a problem.

EDIT

Galaxiom

Please ignore. My Bad as the young fellas say.

 

[CJ_London]

I wasn't suggesting treating a number field as a text - just to assign it to a text box for processing

However 0,000 seems to be the solution - I thought it was 9,999 but there you go

 

[RainLover]

quick addendum - the data type I am referring to is in your source table, not the form.

Finally, consider changing the field type to text, setting a validation rule to only allow, 0-9,.

you can include in calculations using the val function

I wasn't suggesting treating a number field as a text - just to assign it to a text box for processing

You have made the suggestion here, or am I reading this wrong.

 

[RainLover]

No I haven't read this wrong because you suggest the use of the val function.

 

[Icepick]

@RainLover: Long Integer is out of the question, I need larger numbers than its limits (otherwise I wouldn't need more than 11 digits in certain cases). Thanks for the details about the different data types and their space usage

@Galaxiom: I've tried many custom Format values and I've just tested yours too. They all work as expected, they display the formatted value accordingly (yours displays leading 0's, to avoid that I would have used "#,###" which is pretty much equivalent to "Standard"). Whether it is in my form or directly in the table it still does what I've described, when I click to edit this value Access replace the complete number by a scientific notation representation of it if it has more than 11 digits, no matter the type of the field (Double, Decimal or Currency) this behavior is the same. Once I move out of the field it is formatted correctly according to the Format value (no more scientific notation). So the Format is working as expected, it's just the value that is displayed while you're editing it that is not displaying as I expect. This limit changes to 15 digits when you're not using thousands separators (i.e. "Fixed" format), once you put the 16th digit, move out of the field and try to edit it again it will have the same behavior.

I guess that's how Access handles the entry of large numbers; I just expected that there would be a way to disable this behavior as it can be confusing for users who are not familiar with scientific notation.

 

[CJ_London]

Rainlover

Think we have mixed terminology. There is more than one way to solve a problem

 

[RainLover]

Rainlover

Think we have mixed terminology. There is more than one way to solve a problem

Text is Aplha Numeric and is a Data Type
A Text Box is a Control on a Form.

Where do we disagree?

 

[RainLover]

@RainLover: Long Integer is out of the question, I need larger numbers than its limits (otherwise I wouldn't need more than 11 digits in certain cases). Thanks for the details about the different data types and their space usage

I can give you this.

123,456,789,123,456.4320 That is 15 leading digits and 4 Decimal Places. The 4 Decimal places always show.

The set up is.

Data Type "Currency"
Format is "Standard"
Decimal Places is "4"

Will this do what you need.

 

[RainLover]

IcePick

I did another read. You say that you can enter more than 15 Digits.

Well I can't do that.

I will monitor for further developments.

 

[CJ_London]

@Rainlover

Icepick needs more digits than can be supplied through a double data type.

Where would you go from there other than text?

 

[RainLover]

Galaxiom wrote this.

For more significant digits use a Decimal field type which will handle 28 significant digits.

I am not sure we have finally found the best solution. I am sure Galaxiom will be back.

 

[Icepick]

Not really, it does not fix the issue I'm talking about. If you enter "123456789123456.4320" in such field and you save the record, when you click on that value to modify it, Access displays it as scientific notation for you to modify it (then you can enter whatever valid numeric value, but at first it still show as scientific notation, if there are more than 11 digits).

I'm sorry if I'm repeating myself (maybe my explanation isn't clear enough), but my issue is not formatting or sizing the field. I'm aware of the available data types, their limits and how to format them for display, my problem is that when someone tries to edit a formatted value the number shows at first as scientific notation when I would like it to always show as a complete number, without scientific notation.

I have made the field you've suggested and I've recoded the problem (which still happens) and made a GIF to show it :

hxxp://i.imgur.com/ftJNtud.gif

(Sorry my account is too recent and I can't post a clickable link)

Maybe it will be more clear like this. The behavior in the second and third lines is what I'm expecting to happen in the first line, but it always switches to scientific notation if there are more than 11 digits (despite the Format which you can see is set to "Standard" as it shows the thousands separators). Lastly I've entered the value manually to show you the behavior, the value is not entered as a scientific notation, yet when you edit it again Access displays it as such until you save the record again (no matter which format is set for the field).

EDIT: In this sample, don't mind the ' as thousands separators, it's because my Regional settings are set to French standards.

 

[Icepick]

@Rainlover

Icepick needs more digits than can be supplied through a double data type.

Where would you go from there other than text?

I don't, Double, Currency or even Decimal fit my needs. The data stored in these fields are amounts of money so Currency was my first choice and its range is enough to represent the data I want... it's just that with larger numbers the scientific notation kicks in when you are editing a value.

And yes your solution with a Text field is the only one yet that would work but it require quite more work and in terms of database design I don't like representing numbers used in arithmetic operations as Text.