SteveL13

asked on

# Rounding question

I have three fields on a form and a 4rd field that I want calculated UP to the nearest whole number. But there will be two different situations.

Situation #1:

Field "txtOrder_Qty" = 16

Field "txtApplied" = 11

Field "txtPatterns_On" = 2 (note that this is a even number)

The 4rd field, named "txtBalToMake" should be calculated to result in "6". The reasoning behind this is that 16 - 11 = 5 but because "txtPatterns_On" = 2 the result which would be 5 has to be rounded up to 6.

However, if Field "txtPatterns_On" = 1 (note this) then the resulting calculated field should be 5. (no rounding)

I have no idea how to code this in the control source of "txtBalToMake". Or maybe it has to be some kind of if/then VBA code in the onprint event of the detail section of the report?

--Steve

Situation #1:

Field "txtOrder_Qty" = 16

Field "txtApplied" = 11

Field "txtPatterns_On" = 2 (note that this is a even number)

The 4rd field, named "txtBalToMake" should be calculated to result in "6". The reasoning behind this is that 16 - 11 = 5 but because "txtPatterns_On" = 2 the result which would be 5 has to be rounded up to 6.

However, if Field "txtPatterns_On" = 1 (note this) then the resulting calculated field should be 5. (no rounding)

I have no idea how to code this in the control source of "txtBalToMake". Or maybe it has to be some kind of if/then VBA code in the onprint event of the detail section of the report?

--Steve

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

This worked perfectly. Now I just wish I understood what the code means.

Thank you.

Thank you.

It divides by txtPatterns_On, rounds up (that's the -Int(-SomeValue) part), then multiplies with txtPatterns_On.

When txtPatterns_On = 2 that will for an even value do nothing as there is no decimal part to round up.

For an odd value, however, the rounding up will raise the value by 0.5 which - multiplied by 2 - adds a total of 1 to the value.

Of course, if txtPatterns_On = 1 then nothing happens other than txtApplied is subtracted from txtOrder_Qty.

/gustav

When txtPatterns_On = 2 that will for an even value do nothing as there is no decimal part to round up.

For an odd value, however, the rounding up will raise the value by 0.5 which - multiplied by 2 - adds a total of 1 to the value.

Of course, if txtPatterns_On = 1 then nothing happens other than txtApplied is subtracted from txtOrder_Qty.

/gustav

ASKER

Perfect. Thank you for the explanation. Very much appreciated.

--Steve

--Steve

You are welcome!

/gustav

/gustav

ASKER

If "txtPatterns_On" is an even number then

txtBaltoMake = (txtOrder_qty - txtApplied) / txtPatterns_On (rounded up to nearest whole number)

Else

If "txtPatterns_On" is an odd number then

txtBaltoMake = (txtOrder_qty - txtApplied) / txtPatterns_On (no rounding)

EndIf

Does this make it clearer?

--Steve