MS Access If Function

97 views Asked by At

I'm not using SQL at this point, I'm just trying to define a function using query design function in MS Access 2010.

What i'm trying to do: So turns out that I have a 5 month spread (Jan,Feb..May) where each month is a column. Turns out that at times May has a value and January does not, but it should. All the values are either one or null.

What I'm trying to do is write an if function of this sort:

Jan15new: Iff([May-15]=1,[Jan-15]=1,[Jan-15])

However, when I run the query with this iff function I got a column full of negative ones that doesn't abide by the rules of this if function.

If you can shed somelight that would be great! thanks,

2

There are 2 answers

0
Rachel Hettinger On BEST ANSWER

This formula returns a 1 if May-15 = 1, otherwise returns whatever value is in Jan-15:

Jan15new: IIf([May-15]=1,1,[Jan-15])
0
phoog On

If the values in your formula are Boolean values, you do not need to compare them to anything, and you should not be comparing them to numbers:

Jan15new: IIf([May-15]=True,[Jan-15]=True,[Jan-15])

That's actually meaningless, because it is equivalent to this:

Jan15new: IIf([May-15],[Jan-15],[Jan-15])