Conditional Rounding 1

[jennilein]

The following is a statement I have in excel:

=IF(R1<5.01,CEILING(R1,0.1),IF(R1<10.01,CEILING(R1,0.25),IF(R1<50.01,CEILING(R1,0.5),IF(R1<200.01,CEILING(R1,1),IF(R1<500.01,CEILING(R1,2),IF(R1<1000.01,CEILING(R1,5),IF(R1>1000,CEILING(R1,10))))))))

How can I create the same in Access? Please keep in mind, I am not a programmer and typically write all my queries in design view. I've used the ROUND command before, but only to round off to two decimal places - not with conditions as stated above.

HELP! .... thanks in advance to all!

 

[bhoran]

Access does not round the same way as Excel but I have seen a thread or FAQ that shows how to reproduce that type of rounding in Access.

Sorry I can't be of more help but it is a bit complex for me.

 

[JonFer]

In Access, the IIf() function does the same thing and you can nest them. As bhoran mentioned, you should search for rounding posts. The below doesn't round like you want but shows how IIf works.

IIf([FieldName]<5.01,Round([FieldName],2),IIf([FieldName]<10.01,Round([FieldName],1),Round([FieldName],0)))

 

[cheerio]

CEILING is not a native Access function.

If you have Excel on the same PC you can define a User Defined Function in VBA that accepts an input and returns the CEILING value:

Public Function MyCeiling(inputval As Double, roundval As Double) As Double
MyCeiling = Excel.WorksheetFunction.Ceiling(inputval, roundval)
End Function

The trick is when in the VBA window, add a reference to the Excel object library. This will then make the CEILING function available to vba.

Once you have the User defined function you can use it from an Access query screen.

Personally, I would avoid all those nested IF functions by bringing that logic into the VBA as well, but that's another story.

 

[jennilein]

Thank you all for your tips...

However, it's really not your typical rounding. I would like for everything under $5.00 to round to the nearest 0.10 cents. $10 and under to the nearest 0.25 cents, under $50 to the nearest 0.50 cents and so on...

The last post from cheerio seems to be what would work for me, however, I don't really know how to write things in VBA. Where do I put this code? How do I make reference to the Excel object library? How could I write the rules without using IIF?

Thanks so much....

 

[cheerio]

In the database window press ALT and the F11 function key to transfer yourself to the VBA world.

Then do Insert, Module

Then go to Tools, References

Scroll down the list till you find something like

Microsoft Excel 9.0 object library

(the actual version depends on your version of Office :- 9.0 is Office 2000)

Tick the library and click OK

You should have a Window with General and Declarations at the top.

Depending on your settings it might have commands such as

Option Compare Database
or
Option Explicit

Below any such commands paste my 3 lines of code above into that window.

You can now close the VBA window.

The function MyCeiling is now available for use in that Access database in the same way as any other Access function.

As drafted there is no error checking as I just wanted to show the principle. We can talk about that once the basic idea is working for you. Also we can talk about how the nested IFs could be handled.

What I would like you to do at this stage is just get the ceiling function working without the IFs to prove we got that right.

Although VBA coding is a major topic in its own right, the creation of little custom functions is not too difficult.

 

[jennilein]

cheerio-

Thank you soooo much! It works just the way i wanted it to!

All my IIF statements work with the ceiling function, but you recommended not writing it that way. Why? How else would I write those statements? What would the advantages/disadvantages be?

Thank you very much for all your help!

 

[cheerio]

Thanks for the star - glad it worked.

You could replace the nested IF by something like this in VBA:

Public Function BigRound(InputValue As Double) As Double

If InputValue < 5.01 Then
BigRound = MyCeiling(InputValue, 0.1)
ElseIf InputValue < 10.01 Then
BigRound = MyCeiling(InputValue, 0.25)
ElseIf InputValue < 50.01 Then
BigRound = MyCeiling(InputValue, 0.5)
ElseIf InputValue < 200.01 Then
BigRound = MyCeiling(InputValue, 1)
ElseIf InputValue < 500.01 Then
BigRound = MyCeiling(InputValue, 2)
ElseIf InputValue < 1000.01 Then
BigRound = MyCeiling(InputValue, 5)
ElseIf InputValue > 1000 Then
BigRound = MyCeiling(InputValue, 10)
Else
BigRound = InputValue ' should not happen
End If

End Function

First it's easier to read and check once you see the pattern.

Secondly if you often use this complex test you define it once in VBA and then just call BigRound whenever you need it in much the same way as you are now calling MyCeiling.

 

[jennilein]

Cheerio -

Once again - you've made things easier for me!
Thank you for all your support!

 

[straybullet]

A big thank you to cheerio for pointing me in the right direction! I have one problem however, I am getting a runtime error 424 message - Object Expected at the following line:
MyCeiling = Excel.WorksheetFunction.Ceiling(inputval, roundval)

Any suggestions?

Let them hate - so long as they fear... Lucius Accius