Update Query "IIf" and "Right" Function

[Pepsi Girl & Petree]

A friend and I are learning these functions in our computer class and our
teacher has no clue what he's doing. So if anyone could help us it would help
out a lot.

So we have to make an Update Query to update a field using the IIf function
with the following specifications. We have to divide "Street No" field by 2
then multiply that result by 10 (here's when we get lost) then we are
supposed to use the "Right" function to select the units position of the
resultant value. Then Compare to see if the result is equal "0". The truepart
is "even" and falspart is "odd". So here is what we have:
IIf(Right((RegTbl![Street No]/2)*10,0),"Even","Odd")
So when we do this and run it everything comes out even because of the zero
after the 10. Our question is if that's not supposed to be a zero what number
should it be and where would we put the comparing to zero part? Any help
would be most appreciated.

Thanks!

Pepsi Girl & Petree

 

[Dennis]

Your expression needs to be

IIf(Right((RegTbl![Street No]/2)*10,1)=0,"Even","Odd")

 

[raskew via AccessMonster.com]

If the field (either Integer of Text) is Even, the result of your calculation
will always be Even. Conversely, if the field is Odd, the result of your
calculation will be Odd. Can't see any reason to use the Right() function.
Example from the debug (immediate) window:

x = 57
? iif(10*(x/2) mod 2 =0, "Even", "Odd")
Odd

x = 58
? iif(10*(x/2) mod 2 =0, "Even", "Odd")
Even

A friend and I are learning these functions in our computer class and our
teacher has no clue what he's doing. So if anyone could help us it would help
out a lot.

So we have to make an Update Query to update a field using the IIf function
with the following specifications. We have to divide "Street No" field by 2
then multiply that result by 10 (here's when we get lost) then we are
supposed to use the "Right" function to select the units position of the
resultant value. Then Compare to see if the result is equal "0". The truepart
is "even" and falspart is "odd". So here is what we have:
IIf(Right((RegTbl![Street No]/2)*10,0),"Even","Odd")
So when we do this and run it everything comes out even because of the zero
after the 10. Our question is if that's not supposed to be a zero what number
should it be and where would we put the comparing to zero part? Any help
would be most appreciated.

Thanks!

Pepsi Girl & Petree

 

[John Spencer]

Try

IIf(Right((RegTbl![Street No]/2)*10,1),"ODD","EVEN")

Your right criteria was returning a zero length string since you asked for the
character in the zero position (counting from the right). Your calculation
is hopefully going to return zero (false) or 5 (True - any non-zero value is
treated as true). SO you also need to reverse the 2nd and 3rd arguments of
your IIF expression.

Another problem is if Street No is null or has a value that can't be treated
as a number - 1234 1/2 or 1234A

Although a lot clearer and simpler would be
IIF(RegTbl![Street No] MOD 2 = 0, "Even","Odd")
And even that can fail if



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Klatuu]

One thing you have right. Your instructor is incompetent.

But what he is doing is trying to show you the use of functions. But the
calculation is insane.

IIf(Right((RegTbl![Street No]/2)*10,0),"Even","Odd")
Should be

IIf(Right(Cstr((RegTbl![Street No]/2) *10)), 1) = 0,"Even", "Odd")

But the the better way (doesn't follow his instructions) would be:

IIf(RegTbl![Street No] Mod 2 = 0, "Even", "Odd")

And point out to your instructor that names should not contain spaces. They
should only contain letters, numbers, and the underscore character. Also a
naming standard should be in place that avoids the possiblity of a name being
used that is an Access or SQL reserved word.