By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,529 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Access 2007 IIF statement

P: 7
In my query, I am trying to return a negative value as 0. Below is my statement and it just does not return any records with a value less than 0. What did I miss? Thanks

IIf([QtyInStock]<=0,0,[QtyInStock])
Jan 28 '14 #1

✓ answered by Seth Schrock

In the line of buttons on the top of the text editor (what you type your post into), you will see the B, I, and U buttons. Just three buttons to the right is a [CODE/] button. If you highlight the text in your post that is code and then click the [CODE/] button, it will place the code tags around your code so that it is formatted for easier reading and also to preserve spacing.

Back to your question... I'm not seeing the IIF anywhere in your SQL code, so I'm not sure of how you are using it. The correct way would be
Expand|Select|Wrap|Line Numbers
  1. SELECT [Model #]
  2. , UPC
  3. , IIF(QtyInStock <= 0, 0, [QtyInStock]) AS Qty
  4. , Brand
  5. FROM ATGStandard2;
Side note, you shouldn't ever use special characters in field names as it can cause problems. Not always, but it is best practice to steer clear of them.

Share this Question
Share on Google+
5 Replies


Seth Schrock
Expert 2.5K+
P: 2,931
It sounds like you put this in the WHERE clause of the query or you have something else in the WHERE clause that is limiting what records are being returned if the query isn't returning those records. Please provide the full SQL code (using CODE tags, the [CODE/] button)
Jan 28 '14 #2

P: 7
Here is the SQL from access:

Expand|Select|Wrap|Line Numbers
  1. SELECT ATGStandard2.[Model #], ATGStandard2.UPC, ATGStandard2.QtyInStock, ATGStandard2.Brand
  2. FROM ATGStandard2;
I am not familiar with code tags....
Thanks
Jan 28 '14 #3

Seth Schrock
Expert 2.5K+
P: 2,931
In the line of buttons on the top of the text editor (what you type your post into), you will see the B, I, and U buttons. Just three buttons to the right is a [CODE/] button. If you highlight the text in your post that is code and then click the [CODE/] button, it will place the code tags around your code so that it is formatted for easier reading and also to preserve spacing.

Back to your question... I'm not seeing the IIF anywhere in your SQL code, so I'm not sure of how you are using it. The correct way would be
Expand|Select|Wrap|Line Numbers
  1. SELECT [Model #]
  2. , UPC
  3. , IIF(QtyInStock <= 0, 0, [QtyInStock]) AS Qty
  4. , Brand
  5. FROM ATGStandard2;
Side note, you shouldn't ever use special characters in field names as it can cause problems. Not always, but it is best practice to steer clear of them.
Jan 28 '14 #4

P: 7
Works perfect! Sorry not that good yet with SQL.... Had my IIF statement on the criteria line in the design view. My bad that I neglected to include that piece of info in my original post.

Thanks!
Jan 28 '14 #5

Seth Schrock
Expert 2.5K+
P: 2,931
Glad to be able to help.
Jan 28 '14 #6

Post your reply

Sign in to post your reply or Sign up for a free account.