Solved

IIF(And statement in Access query or SQL

Posted on 2008-10-14
2
515 Views
Last Modified: 2010-04-21
I have 2 tables one is tbl_Charges & the other tbl_Liability.
The tbl_Charges has a primary key for case# column. The table also has a position column.
The tbl_Liability looks like this.
BeginDate      EndDate      Position      Daily_$
7/1/2005      6/30/2006      ISR      $136.63
7/1/2006      6/30/2007      ISR      $140.73
7/1/2007      6/30/2008      ISR      $144.95
7/1/2008      6/30/2009      ISR      $150.75
7/1/2009      6/30/2010      ISR      $157.53
7/1/2005      6/30/2006      ISW      $116.58
7/1/2006      6/30/2007      ISW      $119.77
7/1/2007      6/30/2008      ISW      $123.36
7/1/2008      6/30/2009      ISW      $128.29
7/1/2009      6/30/2010      ISW      $134.06
7/1/2005      6/30/2006      ISW Lift      $127.91
7/1/2006      6/30/2007      ISW Lift      $131.75
7/1/2007      6/30/2008      ISW Lift      $135.70
7/1/2008      6/30/2009      ISW Lift      $141.12
7/1/2009      6/30/2010      ISW Lift      $147.47
7/1/2005      6/30/2006      ISR N      $143.42
7/1/2006      6/30/2007      ISR N      $147.75
7/1/2007      6/30/2008      ISR N      $152.15
7/1/2008      6/30/2009      ISR N      $158.24
7/1/2009      6/30/2010      ISR N      $165.36
7/1/2005      6/30/2010      Unknown      $0.00

The only unique identifier in this table is the Liability$.
I need a query that will look at the tbl_Charges, columns Position & Date.
If tbl_Charges!Date is between tbl_Liability!BeginDate And tbl_Liability!EndDate and if tbl_Charges!Position = tbl_Liability!Position.
Output should be If the date of charge is 10/1/2008 and the position is an ISR, it would display $150.75
0
Comment
Question by:HA94694
[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 Comments
 
LVL 45

Accepted Solution

by:
aikimark earned 350 total points
ID: 22715375
No IIF required.

Select [Daily_$]
From tbl_Liability INNER JOIN tbl_Charges ON tbl_Charges.Position = tbl_Liability.Position
Where tbl_Charges.ChargeDate Between tbl_Liability.BeginDate And tbl_Liability.EndDate
0
 

Author Closing Comment

by:HA94694
ID: 31506035
Absolutely perfect. I so did not think of that, obviously. I had tried everything else.
0

Featured Post

Independent Software Vendors: 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.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

717 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