Rounding Down

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to round down an expression in a query, for example: Weeks Completed:
(Now()-[Start Date])/7 needs to return the next lowest whole number.
 
Change your division operator to \ vice /

The first will do INTEGER division which means it will drop the decimal portion.
You can also look at the Int or Fix function which also return the integer
portion of the number. If the number is negative then you should read the help
on the two functions to see which result you want returned.

Int((Now()-[Start date])/7)
 
John,
Sorry to bother you.

Along somewhat similar lines to this thread...

I have a simple query expression in Access 2003 of:

5-yr Ave Return decile: Int([5-yr Ave Return Cat Rank]/10)

When I run the query, it says,
"compile error in query expression 'Int([5-yr Ave Return Cat Rank]/10)'

If I try:
5-yr Ave Return decile: round([5-yr Ave Return Cat Rank]/10,0)
, I get a similar compile error.

Any help to tell me what I did wrong will be greatly appreciated.
Thanks!

John Spencer (MVP) said:
Change your division operator to \ vice /

The first will do INTEGER division which means it will drop the decimal portion.
You can also look at the Int or Fix function which also return the integer
portion of the number. If the number is negative then you should read the help
on the two functions to see which result you want returned.

Int((Now()-[Start date])/7)
I need to round down an expression in a query, for example: Weeks Completed:
(Now()-[Start Date])/7 needs to return the next lowest whole number.
Click to expand...
Click to expand...
 
No idea. Try breaking it down into steps and see where it breaks down.

5-yr Ave Return decile: [5-yr Ave Return Cat Rank]
IF that works try
5-yr Ave Return decile: ([5-yr Ave Return Cat Rank]/10)
IF that works try
5-yr Ave Return decile: Int([5-yr Ave Return Cat Rank])

Do you have any NULL values in [5-yr Ave Return Cat Rank]? If so try wrapping
it in an NZ function.

5-yr Ave Return decile: Int(Nz([5-yr Ave Return Cat Rank],0)/10)


PZ said:
John,
Sorry to bother you.

Along somewhat similar lines to this thread...

I have a simple query expression in Access 2003 of:

5-yr Ave Return decile: Int([5-yr Ave Return Cat Rank]/10)

When I run the query, it says,
"compile error in query expression 'Int([5-yr Ave Return Cat Rank]/10)'

If I try:
5-yr Ave Return decile: round([5-yr Ave Return Cat Rank]/10,0)
, I get a similar compile error.

Any help to tell me what I did wrong will be greatly appreciated.
Thanks!

John Spencer (MVP) said:
Change your division operator to \ vice /

The first will do INTEGER division which means it will drop the decimal portion.
You can also look at the Int or Fix function which also return the integer
portion of the number. If the number is negative then you should read the help
on the two functions to see which result you want returned.

Int((Now()-[Start date])/7)
I need to round down an expression in a query, for example: Weeks Completed:
(Now()-[Start Date])/7 needs to return the next lowest whole number.
Click to expand...
Click to expand...
Click to expand...
 
John,

Thanks for taking the time to reply.

I tried the different steps and it broke down at INT.

I was told by someone else that this can be caused by a bad or missing DLL
file regarding modules. They said to open any module in design view, or open
the VBA editor by typing Ctrl-G. Select Tools... References from the menu and
that if I couldn't find a missing DLL file to uncheck one then check it back
on. Well, I did that and it works just fine now. I guess that procedure
forces Access to relink the libraries or something along those lines.

Hope this helps someone else with the same problem.


John Spencer (MVP) said:
No idea. Try breaking it down into steps and see where it breaks down.

5-yr Ave Return decile: [5-yr Ave Return Cat Rank]
IF that works try
5-yr Ave Return decile: ([5-yr Ave Return Cat Rank]/10)
IF that works try
5-yr Ave Return decile: Int([5-yr Ave Return Cat Rank])

Do you have any NULL values in [5-yr Ave Return Cat Rank]? If so try wrapping
it in an NZ function.

5-yr Ave Return decile: Int(Nz([5-yr Ave Return Cat Rank],0)/10)


PZ said:
John,
Sorry to bother you.

Along somewhat similar lines to this thread...

I have a simple query expression in Access 2003 of:

5-yr Ave Return decile: Int([5-yr Ave Return Cat Rank]/10)

When I run the query, it says,
"compile error in query expression 'Int([5-yr Ave Return Cat Rank]/10)'

If I try:
5-yr Ave Return decile: round([5-yr Ave Return Cat Rank]/10,0)
, I get a similar compile error.

Any help to tell me what I did wrong will be greatly appreciated.
Thanks!

John Spencer (MVP) said:
Change your division operator to \ vice /

The first will do INTEGER division which means it will drop the decimal portion.
You can also look at the Int or Fix function which also return the integer
portion of the number. If the number is negative then you should read the help
on the two functions to see which result you want returned.

Int((Now()-[Start date])/7)

QVCJDN wrote:

I need to round down an expression in a query, for example: Weeks Completed:
(Now()-[Start Date])/7 needs to return the next lowest whole number.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top