Hi,
I have a table 'WeeklyRentHistory' that I want to search using DLookup to return an amount. The amount is determined by using the StartDate from the table 'WeeklyRentCharge' and comparing it to the EffectiveFrom and EffectiveTo dates in the 'WeeklyRentCharge' table.
For example, I might charge a person the following rents for the date range specified:
Amount | EffectiveFrom | EffectiveTo | ToPresent
$150 | 01/01/2017 | 31/01/2017 | False
$160 | 01/02/2017 | 28/02/2017 | False
$170 | 01/03/2017 | [Blank] | True
So the query I have generates a new weekly rent charge, let's say 01/01/2017 to 07/01/2017 and I want the DLookup to return $150. Fast forward to 05/02/2017 to 11/02/2017 and it should pull in $160 and so on.
I have attached the queries, 'Query3a' and 'Query3b' but haven't quite mastered the DLookup as it is returning the most recent value.
Any help is appreciated.
I have a table 'WeeklyRentHistory' that I want to search using DLookup to return an amount. The amount is determined by using the StartDate from the table 'WeeklyRentCharge' and comparing it to the EffectiveFrom and EffectiveTo dates in the 'WeeklyRentCharge' table.
For example, I might charge a person the following rents for the date range specified:
Amount | EffectiveFrom | EffectiveTo | ToPresent
$150 | 01/01/2017 | 31/01/2017 | False
$160 | 01/02/2017 | 28/02/2017 | False
$170 | 01/03/2017 | [Blank] | True
So the query I have generates a new weekly rent charge, let's say 01/01/2017 to 07/01/2017 and I want the DLookup to return $150. Fast forward to 05/02/2017 to 11/02/2017 and it should pull in $160 and so on.
I have attached the queries, 'Query3a' and 'Query3b' but haven't quite mastered the DLookup as it is returning the most recent value.
Any help is appreciated.