Solved

IIF(And statement in Access query or SQL

Posted on 2008-10-14
2
494 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
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

839 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