473,322 Members | 1,501 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Access 2007 IIF statement

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.

5 1136
Seth Schrock
2,965 Expert 2GB
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
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
2,965 Expert 2GB
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
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
2,965 Expert 2GB
Glad to be able to help.
Jan 28 '14 #6

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

Similar topics

27
by: Wayne | last post by:
I've been clicking around Access 2007 Beta 2 and can't see the custom menu bar designer. Is it in the beta? Maybe I'm blind. The question that comes to mind is: Will custom menu bars be the same...
7
by: wwwords | last post by:
I have a form in Access 2007 containing a subform. The control source of the latter is SELECT DISTINCTROW ., ., ., ., . FROM ; which is undoubtedly correct. However, if I click on the down...
16
by: google | last post by:
In a continuous form the following code is under a button in the form header. In Access 2003 and earlier, this goes to a new record, then adds relevant data to that new record. DoCmd.GoToRecord...
17
by: Neil | last post by:
A client of mine likes some of the new bells and whistles in Access 2007, and is thinking about converting our A03 format MDB to an A07 format file. However, while some of the users have A07, many...
16
by: Neil | last post by:
I posted a few days ago that it seems to me that the Access 2007 rich text feature does not support: a) full text justification; b) programmatic manipulation. I was hoping that someone might...
1
by: BL3WC | last post by:
Hi, I'd created a MDE under Access 2003. It is now under testing stage. Some of the users will use Access 2003 runtime and some will use Access 2007 runtime to run this MDE. I installed the...
0
by: simulationguy | last post by:
I have a database the runs fine in Access 2003 but this routine crashes with error 2105 "You can't go to the specified record" in Access 2007 on the last line !itemNumber.SetFocus Any idea why...
2
by: HSXWillH | last post by:
I'm working in Access 2007 and coming to something that's always caused me problems. I have a form titled frmUsers. There are 3 subforms that list exercise dates and exercise types for each user. ...
4
by: simulationguy | last post by:
I inherited an Access 2003 database runs fine in 2003 but in 2007 I get an error on updating a form. Record Not available on the last statement !ItemNumber.SetFocus Does anyone know why...
6
by: Jane Alford | last post by:
Is there any way to dynamically get information about a table in Access 2007? I know I can do this with SQLServer. I'm linking to SQLServer tables from Access 2007. The SQLServer 2005 SELECT...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.