RoundUp, RoundDown, Trunc

[GShen]

Hi,
Are these reserved words accessible in ACCESS 2000? I keep getting errors saying I cannot use them. I thought I used them in the past, but maybe I was mistaken. HELP says I need to have a .DLL reinstalled but the system admin. says it is for EXCELL not for ACCESS because HELP uses the words SPREADSHEET.
Any help would be appreciated. I will have to jump through hoops to get my report to work if these are not available.

Thanks,

Remember when... everything worked and there was a reason for it?

 

[THWatson]

GShen
Here is a Microsoft link which lists the Access 2000 reserved words.

http://support.microsoft.com/kb/q209187/

RoundUp and RoundDown and Trunc don't seem to be among them. However, since they are functions, perhaps adding a letter in front of them could help avoid a problem.

Tom

 

[GShen]

I guess I asked the wrong question. What I mean is that that are in the ACCESS HELP menu but they are not working on my report. ROUND is not on your list but it surely does work but not TRUNC, ROUNDUP or ROUNDOWN.

Remember when... everything worked and there was a reason for it?

 

[THWatson]

GShen
Interesting. I haven't needed to use RoundUp or RoundDown or Trunc. And you are right, they appear in Help.

However, I just tried to use RoundUp and got the #Name error. Help suggests that there is a missing dll file called msowcf.dll

But a search revealed that I have that on my machine.

Odd. If I find out anything else, I will post back.

Tom

 

[THWatson]

GShen
I did a search at Microsoft Office Online and there does not appear to be a RoundUp or RoundDown function in Access 2000.

I then did a search at the Knowledge Base and there are several articles. One is

http://support.microsoft.com/default.aspx?scid=kb;en-us;196652

I'm not sure what you are trying to achieve. You could use the Int function, which will take the number you are using to the next higher or lower whole number.

Anyway, hope this helps.

Tom

 

[GShen]

THWatson,
thanks for the feedback. I got around the problem. However, a few notes. I had 2 fields on my report which when printed showed their respective values. Both were rounded up. When I created a new field and added them together I got the wrong amount. Both were rounded 1 penny up but together were rounded up only 1 penny in whole. Leaving me off a penny here and there all over the place. Int will not allow me to round 2 decimal positions. So since my figures were ok, I just wanted to trunc them after 2 positions and then add them. Just a note, when you use ROUND, it does not round a .5 up to 1. If you have 1.005 and round to 2 positions you get 1.00. I had to add .001 to each of my calcs to get it to round properly. I ended up rounding the numbers in the stored procedure before sending them to the report and then totaled them.
Thanks again.
Note: I did find that DLL in Office but what do I do with it? Is there something which needs to be executed somehow, somewhere?

Remember when... everything worked and there was a reason for it?

 

[THWatson]

GShen
About that .dll, I have it in my system too. But it appears to me that RoundUp and RoundDown just don't work in Access.

At least, that's what I gather from looking at the Microsoft web site

Tom

 

[MichaelRed]

just guessing, but the functions appear to be from EXCEL, to use them in another app (MSACCESS?) yuou would need to be sure that the (EXCEL?) library was included in the REFERECES for the app.

From a slightly different perspective, the overall discussion in this thread APPEARS to concern CURRENCY values which are being manipulated as single or double data types. MS Access includes the data type "CURRENCY" which does the correct "rounding" per the accounting standards.

MichaelRed

 

[GShen]

THWatson and MichealRed,

Just read through everything both of you offered as help.
thanks,

FYI,
I tried adding in EXCELL 9.0 as a reference to no avail. So I guess I was dreaming when I thought I used this once.

Also, I changed the field from currency to a general number and the rounding still does not round the way you think it would. 1.05 rounded to 1 position makes it 1 not 1.1. Rounding 1.051 to 1 position makes it 1.1.
Maybe there is something at the database level which needs to be changed OR there is a bug in MSACCESS which does not handle rounding the way you would think it does.

Thanks for all your help!
I got around both problems. I am moving on. If you find anything contrary to the rounding rule, please let me know.
That would help me in the future.


Remember when... everything worked and there was a reason for it?

 

[MichaelRed]

Adding Excel as a reference is only a part of the soloution. I had "Ass U Me d" that addig the reference would prompt the further investigation / review of the concept of using the "foregin" functions, in particular the necessary to use the procedures included.

Since you have "moved on", doesn't help for me to go into what you can / could retrieve for yourself - if interested. Also, a brief excursion through search land here (in Tek-Tips) should be able to expose numerous references and variations on the techniques.



MichaelRed