Solved

IIF(And statement in Access query or SQL

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

816 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now