Still floored by the FLOOR function

[Stuart Green]

I am still trying desperately to get FLOOR to work in access without success. On a form I have an unbound box doing a calculation with can go to several decimal places. The value I require must be to 2 decimal places AND round down in all cases e.g. 12.579 should give a true value (not just displayed value) of 12.57 not 12.58. (I obviously want to do the same in queries for reports). To do this in EXCEL you would write it as FLOOR(12.579,0.01) but Access does not give this function. The help suggests that it can be invoked and I have done that and do not get a #name error but I just can't get it to work. Wayne Ryan tried to point me in teh right direction but I failed miserably at the first hurdle. Yours, stumped

 

[WayneRyan]

Stuart,

I don't have Access with me, so have no info.

I think the thread I pointed you to included a
function that someone had made.

A short-term fix could be to use the format
function to generate a string "nnnnn.ddd"
Then you could use the Mid function to strip
off characters from 1 to the "." +2

At least this will get someone looking at
this again.

hth,
Wayne

 

[Rich]

Function Floor(N, ByVal Precision)
'
' Similar to Excel's Floor function
' Rounds down (toward zero) to the next higher level of precision.
' Precision cannot be 0.
'
Precision = Abs(Precision)
Floor = Int(N / Precision) * Precision
End Function

 

[Stuart Green]

What stars! Now why couldn't MSoft have made this easy. Function worked a treat. In my form I can now do the calculation in the data source as =FLOOR(([field1]*[field2]),0.01) and get the right answers. Thank you very much!