• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

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.
0
Dr.Abdulwahab Al-anesi
Asked:
Dr.Abdulwahab Al-anesi
  • 6
  • 4
  • 2
  • +3
3 Solutions
 
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-anesiAuthor 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now