Stop Access from rounding 3

[TammyT]

I have a field in a table that is a data type Number, Field Size is Integer (is there a better choice?), Format is Percent & Decimal Places = 2

When I enter a number into the table it is automatically rounded & multiplied by 1000!! What am I missing here?

For example: I type in 94.8 & get 9500.00%, or I type in 10.6 & get 1100.00%

How do I fix this?

Thanks!

 

[PHV]

Field size should be either Single, Double or Decimal.
The Percent format automatically multiply the value by 100, so you have to enter 0.948 to get 94.8%

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TammyT]

Thanks - changing to double worked!

As for the x 100 part - the data is being entered via a form, by various people. Is there anyway to set the field properties on the form (or the input mask or something) so that they can type in 94.8, & it will be saved as 94.80%?

 

[LauryBurr]

Hi TammyT from the old country (that's England...)

It's certainly possible to let the user type in a number (say 94.8) and get Access to interpret it as a percentage without the usual 'multiply by 100' logic (i.e. it saves it as 94.80 and not as 9480%) PROVIDED that all users know this. (In other words you don't want some clever-clogs typing in 0.948 and expecting it to appear as 94.8%, only to find it stays as 0.95%!!)

The way to do it is with a bit of VBA coding - I hope you haven't switched off at this point! (I guess it could also be done by a macro but Ihaven't written one for years, as VBA is so much more powerful!!)

Set up an 'on lost focus' event for the text control into which the figure is to be typed, and create code something along these lines (substituting field names etc - I'm assuming the control on the from is called txPercent):

txPercent_LostFocus()

If IsNull(Me!txPercent) then Exit Sub
Me!txPercent = Me!txPercent/100

End Sub

If the percentages can only be within a certain range you can add some checks so that you can tell whether the number that's typed should be divided by 100 or not (for example, if a figure >100% is impossible then any number from 1 to 99.99 is to be interpreted as 1% to 99.99%; but it still leaves the problem that 0.5 could be interpreted as 0.5% or 50%.)

Hope this helps rather than confuses....

 

[TammyT]

Not at all confused - it's perfect! I put it in the VBA & it works great. Thanks!

Okay - here's another one for you then!

There's a field called PMNumber (for Performance Measure Number). It's actually a text field, because one of the measures has 2 parts - 11a & 11b - so I had no choice (right?). Okay, the only way to sort them "ascending" is to put a 0 in front of any number less than 10, so I have to type in 01, 02, etc. Is there some simple code so that the users can type in 1,2,3, etc & Access will convert? I'm sure there is, but I haven't had any formal VBA training, so I'd rather hear it from an expert than guess!

Thanks again from across the pond!

+Tammy

 

[PHV]

ORDER BY Val(PMNumber), PMNumber

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LauryBurr]

Hey this is good!

Glad my tip was helpful Tammy; by the timw I got home from my client (from where I answered your query - honest, I was looking for an answer to my client's problem!!) I found your followon question and PHV's answer. Good for both of us as I've somehow managed to be around for several (don't ask!) years without discovering the Val function. Every day is an education...

More fun arises when the letters come before the numbers, but that's another story!

All the best

Hope your part of 'across the pond' isn't the wet part!!