Please Help - I Give Up!!!

I couldn't find a similar question already answered and I am stuck at home due to the blizzard here in Denver Colorado so I can't get any other resources to help. I am working on a select query where I am using two table's and another select query to pull information.

query HOLDINGS HASH QUERY #3:
- AS_OF_DATE
- SAP
- ACCNTNUM
- SumOfSumOfAS_OF_BOOK_VALUE_4TH

table HOLDINGS HASH CGAAP ADJUSTMENT TABLE:
- BEGIN DATE
- END DATE
- SAP
- ACCNTNUM
- SUM VALUE

table SAP DETAIL TABLE:
- Fiscal Year
- Company Code
- Account Number
- SumofAmt CoCd currency
- Cabinet

Currently the join properties include: from SAP DETAIL TABLE the Company Code and Account Number to the HOLDINGS HASH QUERY #3 SAP and ACCNTNUM, from HOLDINGS HASH QUERY #3 SAP and ACCNTNUM to the HOLDINGS HASH CGAAP ADJUSTMENT TABLE SAP and ACCNTNUM.

There are three things that I am trying to accomplish with this query. The first is pulling down the SAP and ACCNTNUM fields which works fine. The second is pulling down the SumOfAmt CoCd currency and SumOfSumOfAS_OF_BOOK_VALUE_4TH which matches the SAP and ACCNTNUM fields which works fine.

What I'm trying to do for the third is to only pull the SUM VALUE from the HOLDINGS HASH CGAAP ADJUSTMENT TABLE that matches the SAP and ACCNTNUM fields and also where the AS_OF_DATE from the HOLDINGS HASH QUERY #3 is between the BEGIN DATE and END DATE from the HOLDINGS HASH CGAAP ADJUSTMENT TABLE.

This query will then be the output for a report.

In order to accomplish the third task I have tried to complete a SELECT statement and a IIF statement that have not worked. I am sure the select statement would work but I just can't figure it out. Any help would be very very much appreciated.

Sam