Solved

# Correction of odd number to even number

Posted on 2009-12-29
484 Views
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.
0

LVL 28

Accepted Solution

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

Author Comment

Thank you peakpeak I do not have enough experience in VB how to do it use the expression Builder in the Query
0

LVL 46

Assisted Solution

Hi awahab595,

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

Gary
0

LVL 48

Assisted Solution

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

LVL 46

Expert Comment

Why the 2 minuses?
Basically the same as mine
0

LVL 48

Expert Comment

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

LVL 28

Expert Comment

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

LVL 46

Expert Comment

In that case you should use CLNG
= -CLNG(-(Field1) / 2) * 2
0

LVL 48

Expert Comment

That won't work. CLng rounds 0.5+ up for odd integers.
Also, CLng is VBA thus (marginally) slower than INT.

/gustav
0

LVL 46

Expert Comment

Int might not be big enough..
I'd go for
=ROUND(Field1/2,0)*2
or
=CLNG(Field1/2)*2
0

LVL 48

Expert Comment

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

LVL 9

Expert Comment

update table
set field = field + 1
where field MOD 2 = 1
0

LVL 1

Expert Comment

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

LVL 46

Expert Comment

Saved in hotmail ??
0

LVL 46

Expert Comment

Points should be split
0