MS Access If Function

Question

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,

Can you supply some of your sample data for May and Jan? Is that the statement you have?? IFF is not an access statement, IIF is. Also there appears to be a stray = in the true part of your statement. Can you post the statement exactly as it appears? — Gene
– Gene, Commented Jun 8, 2015 at 21:16
The numerical value of True in Access and Visual Basic is -1 (in other words, all bits are set). This is in contrast to most other languages (C-derived languages, for example) and database systems (SQL Server, for example), where it is 1. — phoog
– phoog, Commented Jun 8, 2015 at 21:31

Rachel Hettinger · Accepted Answer · 2015-06-08 21:19:40Z

1

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])

answered Jun 8, 2015 at 21:19

Rachel Hettinger

8,4872 gold badges24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

phoog · Accepted Answer · 2015-06-08 21:35:41Z

0

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])

answered Jun 8, 2015 at 21:35

phoog

43.2k6 gold badges84 silver badges125 bronze badges

Collectives™ on Stack Overflow

MS Access If Function

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related