• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

IIF(And statement in Access query or SQL

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
HA94694
Asked:
HA94694
1 Solution
 
aikimarkCommented:
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
 
HA94694Author Commented:
Absolutely perfect. I so did not think of that, obviously. I had tried everything else.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now