Round up to nearest even integer

[Guest]

I'm a beginner trying to use access to manipulate lumber lists. I've got a
couple rounding questions. My main lumber table has thickness, width and
length fields. The values are in mm. I need to order lumber in feet,
rounded up the the next even integer. The conversion is easy:

[thickness] / 25.4

Rounding that up the the next even foot is tougher. I can get it rounded up
to the next integer easy enough by:

Round((([40L]/25.4)/12)+0.5,0)

But I'm stumped on how to use an expression to round up to the next even
foot. Would a macro or a vba expression be a better way to go?

Thanks

 

[Albert D.Kallal]

I think a public function in a module would do the trick...

How many deciaml points do you have for the mm mesasurment?

Try the follwing

Public Function MMtoFEET(mm As Currency) As Integer

Dim inches As Currency

inches = mm / 25.4

MMtoFEET = Fix(inches / 12)

' any rouding?
If MMtoFEET <> (inches / 12) Then
MMtoFEET = MMtoFEET + 1
End If


End Function

 

[strive4peace]

Hi Lumpy,

If you are putting this equation in a query, you can do this

field --> Ft: Round((([40L]/25.4)/12)+0.5,0)

field --> FtEven: IIf([Ft] Mod 2=0,[Ft],[Ft]+1)

otherwise, you can make one long equation like this:

round(([40L]/25.4)/12+0.5,0)+IIf(round(([40L]/25.4)/12+0.5,0)
Mod 2=0,0,1)

the MOD function returns a remainder

5 MOD 2 --> 1

Also, it is not a good idea to start a fieldname with a
number, this can cause problems.

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day