decimal place help

[meenctg]

In my table i have a field which is number type and i fixed it decimal place 3 just like this (0.000). Generally it add 1 before decimal place when decimal place reach 1000 then the figure will change like this ( 0.000 to 1.000)

But i have need when decimal place reach 200 it will add 1 before decimal place. it's like ( 0.000 to 0.200 to 1.000)

Please help me to do this task

 

[Beetle]

Not sure I'm following your logic here, but you'd likely need to do this at the form level, using the Before Update event of the form. Something like;

If Me![YourField] > .2 Then Me![YourField] = 1

(use your actual field name of course)

 

[meenctg]

Not sure I'm following your logic here, but you'd likely need to do this at the form level, using the Before Update event of the form. Something like;

If Me![YourField] > .2 Then Me![YourField] = 1

(use your actual field name of course)

Understanding my logic i attached a photo. Please see this and help me kindly

 

[Beetle]

So if the decimal portion of the Sum is greater than .2 you want to round up to the next whole number?

Something like this might work in a calculated query field;

IIf([SumField]-Fix([SumField)>.2, Fix([SumField])+1, [SumField])

The Fix function removes the fractional part of a number (you can also use the Int function for this). Replace [SumField] with the actual name of your field of course.

 

[Galaxiom]

I would do this by creating a custom function to handle the unusual additon. It is tidier that way and the function can be easily applied wherever it is required in the database or project.

BTW The difference between Int() and Fix() is how they handle negative numbers.

 

[meenctg]

So if the decimal portion of the Sum is greater than .2 you want to round up to the next whole number?

Something like this might work in a calculated query field;

IIf([SumField]-Fix([SumField)>.2, Fix([SumField])+1, [SumField])

The Fix function removes the fractional part of a number (you can also use the Int function for this). Replace [SumField] with the actual name of your field of course.

Your thinking is right but i don't understand where i'll use it
( IIf([SumField]-Fix([SumField)>.2, Fix([SumField])+1, [SumField]) )
my field name is STOCK

i created i query for summation that is SumOfSTOCK

 

[Galaxiom]

You can't force the Sum function to add using your unusual arithmetic.

You will need to derive two fields from your original field. One field with the integer part and another with the decimal part. Sum each of them separately.

Then divide the sum of the decimal by 5 and apply Beetle's IIF formula to that result. Then add that result to the Sum of the integer field.