Decimals (1 Viewer)

[ryetee]

I thought it was only dates that I couldn't fathom but now I'm stuck on numbers!

I want to limit the user to 2 decimal places when input on a form. I can only get as far as displaying the rounded 2 decimal places but can't stop him putting in something like 231.1278364731. It will display as 231.13 but i'm storing the whole lot which is screwing things up.

So what should the table entry look like, the form entry and anything else I've missed?

 

[theDBguy]

Hi. Not sure if this will help but have you tried using an Input Mask?

 

[ryetee]

Hi. Not sure if this will help but have you tried using an Input Mask?

I had a quick go. Not sure what input mask I should have but I tried with 99999.99 but it looks like he then has to put in 00012.12 for example which will soon get on his nerves. It also displays that as something like _____.__ which looks naff. I think I can change that display but not explored it deeply enough to get over the main problem of leading zeros.


I suppose i could stick in some event that tests to see if there are more than 2 decimal digits but I've go this all over the place and want access to pull it's weight!

EDIT
OK it's not as bad as I thought. Leading zeros aren't required but if user inputs 1.22 it's initiall display as 1____.22 which when tabbed reverts to 1.22. so if I can replace _ with a space I'm laughing!

EDIT 2

OK so you can put something like this

#9999.99;1; :

which more or less works but not quite as ms office support pages suggest.

The # can be -, +, or a space.
It says 9 has to be a digit, but the user can actually enter a space which then produces an error if it's between digits, i.e 12 3.45. If the space is entered before any digits it works ok but you shouldn't be able to anyway.
The 1 after the first ; indicates that the mask is not stored with the data. A 0 says it is but no idea why you'd want to????
The space between the second ; and the : is the default displayed character, in my case a space!

So how can I prevent them sticking in a space with the above mask. Oh and if they enter a space right at the beginning where the # is then they can't put a '-' in the second position. If I change the mask to ######.99 tyhen they can out all sorts of weird stuff in 1 2+3-.55 for example.

 

[theDBguy]

What is the Input Mask now? If it’s something like “9999.99”;”_”;0 then try replacing it with “9.99”;;

 

[ryetee]

What is the Input Mask now? If it’s something like “9999.99”;”_”;0 then try replacing it with “9.99”;;

That works to some extent but you can only enter 0.01 to 9.99 with no minus. If you put a space at the front it doesn't matter as it's accepted by access and is treated as a zero which you may want but you can put in 1. 2 as well instead of either 1.2 or 1.02

The input mask I have at the moment is #99999.99;1; : which works but see my edited comments above

 

[theDBguy]

Hi. I started my original reply with "not sure if this will help" because I rarely recommend using Input Masks because I know how annoying they can be. I just thought it would be quick enough to try it out, in this case. In the end, you might end up with a code (VBA) solution, if Input Mask cannot provide everything you need. Good luck!

 

[MajP]

How about using a decimal datatype with scale of 2.

 

[ryetee]

How about using a decimal datatype with scale of 2.

I did try it but didn't realise what scale was. I assumed decimal places limited the number to the specified amount. I guess it means what it's rounded up to.
I'll give it a go.

What I have above with the input mask works in the main and the problems I can live with except for one!! I wonder if your way will cure that.

The problem I have is I have a field that are payments made. These are usually +ve numbers and are added to the total amount paid. The total looks good and to the correct number of decimal places. The problem comes when refunds are made. In cases I can end up with a total amount paid going to an amount of say 3.499999 even though the masks are limiting the additions and subtractions to decimal places. What's going on there then

EDIT

OK that clears up the 3.4999999 problem!

 

[theDBguy]

Hi. Not sure what's going on "there," but if we're talking about money data, then the recommended data type to use is Currency.

 

[ryetee]

Hi. Not sure what's going on "there," but if we're talking about money data, then the recommended data type to use is Currency.

it works using scale of 2!
i'm sure i'd tried currency along the way but still had weird results

 

[ryetee]

How about using a decimal datatype with scale of 2.

This works but you still need an input mask because you can still enter more than 2 decimal places but it ignores 3 decimal places onward

 

[Micron]

Why not in the form BeforeUpdate event


[txtTheNumber] = Left([txtTheNumber], InStr([txtTheNumber], ".") + 2)

 

[ryetee]

Why not in the form BeforeUpdate event


[txtTheNumber] = Left([txtTheNumber], InStr([txtTheNumber], ".") + 2)

Got it working using an input mask and defining field as decimal to a scale of 2.

 

[Micron]

I'm no expert on such masks - have managed to avoid them thus far. From what I read, the # will allow plus/minus sign or space but insert a space if not provided. I guess the default would be over-ridden (I'm assuming the field is a number type) if not provided, but what if user inputs + or - ? An error?
I'm biased in this case, so I can't see how the mask is better than what I provided.

EDIT
Had to play, so table field is Single/Fixed/Scale = 2 with #9999.99 mask
In form textbox I enter 123456.789
One time it dropped the 456.789 and entered 1.23 (confirmed in table)
Then next time, 123456.789 became 123456.79 in the form. When I clicked on that table field, it became 123456.80
My aversion to input masks is hereby reinforced.

 

[ryetee]

I'm no expert on such masks - have managed to avoid them thus far. From what I read, the # will allow plus/minus sign or space but insert a space if not provided. I guess the default would be over-ridden (I'm assuming the field is a number type) if not provided, but what if user inputs + or - ? An error?
I'm biased in this case, so I can't see how the mask is better than what I provided.

EDIT
Had to play, so table field is Single/Fixed/Scale = 2 with #9999.99 mask
In form textbox I enter 123456.789
One time it dropped the 456.789 and entered 1.23 (confirmed in table)
Then next time, 123456.789 became 123456.79 in the form. When I clicked on that table field, it became 123456.80
My aversion to input masks is hereby reinforced.

I don't particular like them but I've tested mine to death and all appears ok. There's a system message if they try and do anything stupid which I can live with (although I prefer to cater for it myself)

I haven't the time at the moment to look at yours as I've spent far too long on this already byt when I get a breather I'll give it a go.