RoundUp function

[LeLe]

I am trying to create a round up type function to use in an Access 2003
query. Here is what I have so far.

CutLength: Round((Int(([FLength])/[repeat])+0.99)*[repeat])

It works great as long as the Flength/repeat does not result in a whole
number. As long as the division process leaves something after the decimal
point, the function works great. For example if Flength = 108 and repeat =
10, the function correctly rounds up the 10.8 to 11 and then multiplies by
10 to produce 110 or if the Flength = 104, the function will correctly round
the 10.4 up to 11 and return 110.

Here’s the problem. If Flength = 110 and repeat = 10 the function rounds up
to 12 and produces a result of 120 when 110 is the correct answer.

Is there a simple fix? I hope so, this is only part of my final query.
There are also situations where repeat will = 0 which will destroy the math.
Is this where the nz function would help?

Any help is greatly appreciated.

 

[Allen Browne]

Try:
CutLength: - Int( - [FLength] / [repeat])

 

[LeLe]

Works Great!!! Thanks so much!

Unfortunately there is the possibility that the [repeat] may be null. If
this is the case, [CutLength] should = [Flength]. I tried adding the nz
function

CutLength: - Int( -[FLength]/nz([repeat]))

It made no difference if there was a repeat value, but I got errors when the
repeat field was null.

Thanks again for your help.

Try:
CutLength: - Int( - [FLength] / [repeat])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to create a round up type function to use in an Access 2003
query. Here is what I have so far.

CutLength: Round((Int(([FLength])/[repeat])+0.99)*[repeat])

It works great as long as the Flength/repeat does not result in a whole
number. As long as the division process leaves something after the
decimal
point, the function works great. For example if Flength = 108 and repeat
=
10, the function correctly rounds up the 10.8 to 11 and then multiplies
by
10 to produce 110 or if the Flength = 104, the function will correctly
round
the 10.4 up to 11 and return 110.

Here’s the problem. If Flength = 110 and repeat = 10 the function rounds
up
to 12 and produces a result of 120 when 110 is the correct answer.

Is there a simple fix? I hope so, this is only part of my final query.
There are also situations where repeat will = 0 which will destroy the
math.
Is this where the nz function would help?

Any help is greatly appreciated.

 

[boblarson]

Your errors are because if the [repeat] field is null the NZ function is
changing it to 0 which cannot be a divisor. So, you would have to change it
to either replace with a 1 to divide by 1 (one) or to give it a blank value.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

Works Great!!! Thanks so much!

Unfortunately there is the possibility that the [repeat] may be null. If
this is the case, [CutLength] should = [Flength]. I tried adding the nz
function

CutLength: - Int( -[FLength]/nz([repeat]))

It made no difference if there was a repeat value, but I got errors when the
repeat field was null.

Thanks again for your help.

Try:
CutLength: - Int( - [FLength] / [repeat])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to create a round up type function to use in an Access 2003
query. Here is what I have so far.

CutLength: Round((Int(([FLength])/[repeat])+0.99)*[repeat])

It works great as long as the Flength/repeat does not result in a whole
number. As long as the division process leaves something after the
decimal
point, the function works great. For example if Flength = 108 and repeat
=
10, the function correctly rounds up the 10.8 to 11 and then multiplies
by
10 to produce 110 or if the Flength = 104, the function will correctly
round
the 10.4 up to 11 and return 110.

Here’s the problem. If Flength = 110 and repeat = 10 the function rounds
up
to 12 and produces a result of 120 when 110 is the correct answer.

Is there a simple fix? I hope so, this is only part of my final query.
There are also situations where repeat will = 0 which will destroy the
math.
Is this where the nz function would help?

Any help is greatly appreciated.

 

[Allen Browne]

So you want repeat treated as 1 if it is null?

This should do it, and also avoid the division-by-zero error:
CutLength: IIf([repeat]=0, Null, -Int(-[FLength]/Nz([repeat],1))

This question gets asked reasonable often, so I've added a new page here
explaining about rounding:
http://allenbrowne.com/round.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Works Great!!! Thanks so much!

Unfortunately there is the possibility that the [repeat] may be null. If
this is the case, [CutLength] should = [Flength]. I tried adding the nz
function

CutLength: - Int( -[FLength]/nz([repeat]))

It made no difference if there was a repeat value, but I got errors when
the
repeat field was null.

Thanks again for your help.

Try:
CutLength: - Int( - [FLength] / [repeat])

I am trying to create a round up type function to use in an Access 2003
query. Here is what I have so far.

CutLength: Round((Int(([FLength])/[repeat])+0.99)*[repeat])

It works great as long as the Flength/repeat does not result in a whole
number. As long as the division process leaves something after the
decimal
point, the function works great. For example if Flength = 108 and
repeat
=
10, the function correctly rounds up the 10.8 to 11 and then
multiplies
by
10 to produce 110 or if the Flength = 104, the function will correctly
round
the 10.4 up to 11 and return 110.

Here’s the problem. If Flength = 110 and repeat = 10 the function
rounds
up
to 12 and produces a result of 120 when 110 is the correct answer.

Is there a simple fix? I hope so, this is only part of my final query.
There are also situations where repeat will = 0 which will destroy the
math.
Is this where the nz function would help?

 

[Χ]

καλησπεÏα-4425-86A5-d up type function to use in an Access 2003