Correction of odd number to even number

A rounded calculated field in my query is retuning odd number in some records, I want it to increase the number by one to be even number.
Dr.Abdulwahab Al-anesiProjects Information Technology ManagerAsked:
Who is Participating?
 
peakpeakCommented:
In the table or in forthcoming calculations?
In VBA:
Sub test()
Debug.Print OddToEven(3) 'Prints 4
End Sub

Function OddToEven(Num As Integer) As Integer
If (Num Mod 2) = 1 Then Num = Num + 1
OddToEven = Num
End Function

0
 
Dr.Abdulwahab Al-anesiProjects Information Technology ManagerAuthor Commented:
Thank you peakpeak I do not have enough experience in VB how to do it use the expression Builder in the Query
0
 
tbsgadiCommented:
Hi awahab595,

Something like
=ROUND(Field1/2,0)*2

Gary
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gustav BrockCIOCommented:
In your query, wrap your expression like this (as shown in query design view):

EvenValue: -INT(-(YourCalculatedValue) / 2) * 2

For example, if your expression is: [Some Field] * 4 + 17

EvenValue: -INT(-([Some Field] * 4 + 17) / 2) * 2

/gustav
0
 
tbsgadiCommented:
Why the 2 minuses?
Basically the same as mine
0
 
Gustav BrockCIOCommented:
That's what makes it round up, nice trick. INT by itself rounds down.

Round should be avoided for critical purposes as it is known to be buggy:

http://www.xbeat.net/vbspeed/c_Round.htm

Also, INT is native to SQL, thus much faster.

If this matters in this case we don't know ...

/gustav
0
 
peakpeakCommented:
Create an update query that selects the fields that is odd and add 1 to it. See attached test query that works
updatequery.jpg
0
 
tbsgadiCommented:
In that case you should use CLNG
= -CLNG(-(Field1) / 2) * 2
0
 
Gustav BrockCIOCommented:
That won't work. CLng rounds 0.5+ up for odd integers.
Also, CLng is VBA thus (marginally) slower than INT.

/gustav
0
 
tbsgadiCommented:
Int might not be big enough..
I'd go for
=ROUND(Field1/2,0)*2
or
=CLNG(Field1/2)*2
0
 
Gustav BrockCIOCommented:
It is "enough" whatever that means as is performs the rounding 100% correctly for any numeric value which Access can handle.

What may not be enough are the other suggestions as explained earlier.
The exception is peakpeak's suggestion:

If (Num Mod 2) = 1 Then Num = Num + 1

but that is a bit long-winded. For SQL it would read:

IIF(Num Mod 2 = 1, Num + 1, Num)

/gustav
0
 
jamesguCommented:
update table
set field = field + 1
where field MOD 2 = 1
0
 
mcai4mr2Commented:
If you want to replace all odd numbers to even numbers then: [number] + ([number] mod 2) should do the job

In access' query builder you can put: "EvenNumber: [number]+([number] Mod 2)" in the field criteria where [number] is the (calculated) field/parameter (EventNumber is the name given to the output field)
0
 
tbsgadiCommented:
Saved in hotmail ??
0
 
tbsgadiCommented:
Points should be split
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.