Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query Expression for contentents of a field except some values

Posted on 2014-03-26
4
Medium Priority
?
219 Views
Last Modified: 2014-03-26
I have a table called Accounts. The table has two fields Accounts and Amounts. I am creating a query and need an expression that pulls amounts if the account is not account 1 or 5. Below is a sample of what I need. How do I write the expression?

Accounts      Amounts            Query Result wanted
1      100            0
2      200            200
3      300            300
4      400            400
5      500            0

conernesto
0
Comment
Question by:Conernesto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39956202
If you ONLY want those records:

SELECT  Account , Amount
FROM YourTable
WHERE Account <> 1 and Account <> 5

Open in new window


If you need to display 0 for those amounts:


SELECT  Account , Amount, IIF(Account= 1 OR Account = 5, 0, Amount) AS NewAmount
FROM YourTable

Open in new window

0
 

Author Comment

by:Conernesto
ID: 39956282
Accounts  Amounts    Expression Results wanted

1               100               0
2               200               200
3               300               300
4               400               400
5               500               0

The above is to clerify.
0
 

Author Comment

by:Conernesto
ID: 39956304
Can you provide the formula to enter the above in an expression since I will need to do more with this? For Example: Xpression: IIF([Account_tbl]![Account]?????.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39956327
That's part of the SQL statement....

The expression is:

IIF(Account= 1 OR Account = 5, 0, Amount)

Open in new window



You can assign it a column name like this:

NewAmount: IIF(Account= 1 OR Account = 5, 0, Amount)

Open in new window



(You can toggle between SQL view and Design  view to see it written both ways)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question