Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3333
  • Last Modified:

Microsoft Access - VBA , Round UP to next EVEN whole number

Hi folks,
Trying to round some measurements up to the next "EVEN - WHOLE NUMBER"

2.1 becomes 4
3.7 becomes 4
6.3 becomes 8
7.9 becomes 8

Seems to be a piece of cake in EXCEL with the EVEN function...  can anyone help me with the VBA code to use on a Access 2010 form?
0
TechGuise
Asked:
TechGuise
  • 7
  • 6
  • 5
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Round Up

=-Int(-[YourFieldName])

Round Down:
=Int([YourFieldName])

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Be sure to note the  Minus signs :-)
0
 
TechGuiseAuthor Commented:
Thanks for lightning fast response!  Just tried it.

Me.NewWidth.Value = -Int(-Me.POItemWidth.Value)

22.125 turns into 23
21 just stays at 21

Does what I have look right?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
yep.
Guaranteed to work in all case.
Most other schemes fail at some point.

mx
0
 
TechGuiseAuthor Commented:
Are you sure this will take it to the "NEXT EVEN NUMBER"  (like 2,4,6,8)
0
 
TechGuiseAuthor Commented:
Has something to do with the way the GLASS INDUSTRY charges for cutting glass
0
 
COACHMAN99Commented:
try:
  Dim tmpTXT2 As Double
  tmpTXT2 = Text0
  Text2 = -Int(-Text0)
  If Text2 Mod 2 = 0 Then
    If Text2 - tmpTXT2 < 1 Then
      Exit Sub
    Else
      Text2 = Text2 + 2: Exit Sub
    End If
  End If
  If Text2 = 0 Then Text2 = 2: Exit Sub
  If Text2 = 1 Then Text2 = 2: Exit Sub
  If Text2 \ 2 <> 0 Then Text2 = Text2 + 1
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Sorry ... I missed 'EVEN' ...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
This should work"

-Int(-[YourFieldName]) + (-Int(-[YourFieldName]) Mod 2)

mx
0
 
COACHMAN99Commented:
looks much more elegant :-)
0
 
TechGuiseAuthor Commented:
You guys are awesome!   Both worked great.  Thanks!!
0
 
TechGuiseAuthor Commented:
Can I ask what MOD 2 does?
0
 
COACHMAN99Commented:
Divides two numbers and returns only the remainder
and '\' returns the whole number :-) (sometimes called DIV
0
 
TechGuiseAuthor Commented:
Thank You!
0
 
COACHMAN99Commented:
you're welcome!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Thanks to Gustav Brock for the elegant Round Up/Down functions .... :-)

mx
0
 
COACHMAN99Commented:
hear! hear!   (mine was very 'in-elegant') :-)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
COACHMAN99 ... you approach was fine. But thanks really to Gustav ... The Master of Date and Time, a more elegant approach exists :-)

mx
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 7
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now