DLookup function in Query (1 Viewer)

maff811 · Feb 28, 2017

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.

Ranman256 · Feb 28, 2017

do NOT put Dlookup in a query. The QUERY is the dlookup.
Either with another query or a join to a lookup table.

now, if you want to do Dlookup on a form against the query for 1 value, cool.

sneuberg · Feb 28, 2017

I need practice writing joins that have BETWEENs in them so I'll be working on the query that Ranman256 talks of. It may take a while. Meanwhile here's a DLookUp that get's part of the job done. It doesn't handle the cases where the EffectiveTo date is null.

Code:

RentLookUp:  DLookUp("[RentAmount]","[WeeklyRentHistory]", "#" & [StartDate] & "# BETWEEN [EffectiveFrom] AND [EffectiveTo]")

This expression would go in a query that has the WeekyRentCharge table e.g.

Code:

SELECT WeeklyRentCharge.WeeklyRentID, WeeklyRentCharge.Lodger, WeeklyRentCharge.StartDate, DLookUp("[RentAmount]","[WeeklyRentHistory]","#" & [StartDate] & "# BETWEEN [EffectiveFrom] AND [EffectiveTo]") AS RentLookUp
FROM WeeklyRentCharge;

sneuberg · Feb 28, 2017

When it handles the cases where EffectiveTo is null and the Lodger is put it it starts looking silly.

Code:

SELECT WeeklyRentCharge.WeeklyRentID, WeeklyRentCharge.Lodger, WeeklyRentCharge.StartDate, DLookUp("[RentAmount]","[WeeklyRentHistory]","([Lodger] = " & [Lodger] & " And [EffectiveFrom] <=  #" & [StartDate] & "# And [EffectiveTo] Is Null) Or ([Lodger] = " & [Lodger] & " And  #" & [StartDate] & "# BETWEEN [EffectiveFrom] AND [EffectiveTo] And Not ([EffectiveTo] Is Null))") AS RentLookUp
FROM WeeklyRentCharge;

If I can't get the query worked out it would be better if this was implemented with a public function in a standard module where it would be more maintainable.

sneuberg · Feb 28, 2017

As an aside I notice that you are storing a boolean value named ToPresent in the WeeKyRentHistory. It's value appears to be simply EffectiveTo Is Null. If that's the case then it's better just to used the calculated value and not store it.

sneuberg · Feb 28, 2017

Here's is the query version. This query has operators in the join such as <=,>= and Between which the query designer can't represent in design view. If you open or switch to design view Access will complain about it. This query can only be changed or modified in the SQL view.

Code:

SELECT WeeklyRentHistory.Lodger, WeeklyRentHistory.EffectiveFrom, WeeklyRentHistory.EffectiveTo, WeeklyRentCharge.StartDate, WeeklyRentHistory.RentAmount
FROM WeeklyRentCharge INNER JOIN WeeklyRentHistory ON 
(WeeklyRentCharge.Lodger = WeeklyRentHistory.Lodger)
 AND
 ((WeeklyRentCharge.StartDate BETWEEN WeeklyRentHistory.EffectiveFrom And WeeklyRentHistory.EffectiveTo AND WeeklyRentHistory.EffectiveTo Is Not Null )
 Or
(WeeklyRentCharge.StartDate >= WeeklyRentHistory.EffectiveFrom AND WeeklyRentHistory.EffectiveTo Is Null ));

maff811 · Mar 1, 2017

Hi Steve,

Thanks so much for this.

I need to try and turn this into an append query which includes the code to generate the next week's date range and the amount you have got to work for me... Somehow I'm going to have to merge the code I had previously with the code you wrote so I can make this work from a button on a form.

For example, the lodger has been charged $230 for the period 01/01/2017 to 07/01/2017. When I click a button on a form, I want it to bring up the next week's dates of 08/01/2017 to 14/01/2017 and charge $230.

I'll have a play around now.

THanks again

maff811 · Mar 1, 2017

I'm stumped.

What I want to achieve is the ability to add one week onto the last WeeklyRentCharge and pull in the corresponding RentAmount from the table WeeklyRentHistory based on the StartDate in WeeklyRentCharge being between EffectiveFrom and EffectiveTo or if the EffectiveTo is null, then where the StartDate is greater than EffectiveFrom.

Your query achieves the latter, but I am scratching my head trying to get the first part to work as well.

maff811 · Mar 1, 2017

I should also say that this is determined by the LodgerID which is on an open form

sneuberg · Mar 1, 2017

For a form operation it would probably be better to just use DLookup or a function rather than this complex query. I'll set up a function that will do what you need if you will set up a form with everything you want except for this function and upload your database with this form. This would save a lot of time as there wouldn't be confusion over the control names.

maff811 · Mar 1, 2017

Great Steve, thanks.

The form is already setup to ensure the lodger's name is pulled in from the form which is open in the background.

sneuberg · Mar 1, 2017

You want to " add one week onto the last WeeklyRentCharge ...". The WeeklyRentCharge form in the database does seem to have any way to add a new records. I need a form that is setup up to add the new record. I'd like you to include a button on it the with a name something like GetRent. Just leave the code blank for me to fill in.

sneuberg · Mar 1, 2017

Also please include any forms this form uses like the Logger form it complains about during close or if that's a work in progress comment out any code that refers to forms that don't exist.

maff811 · Mar 2, 2017

Hi Steve,

Sorry for the delay. I have uploaded the DB with more forms included (Just trying to watch the size of the file when uploading).

Start by opening the Navigation Form, then you can select any lodger and then from there select the Add New Rent Charge button. This is where I am trying to add the extra week and pull in the rent amount. You will see this data in the tabs Rent Charges History and Rent History.

sneuberg · Mar 2, 2017

I changed my mind after getting into this as I could see it would be easier to do the bulk of this in a query. This query has the same logic as the previous query I provided but as it's using the form data what was in the join of the previous query was moved to the WHERE clause. Now you can view this in design view and I hope you can see from that how it works. The SQL of the query is:

Code:

SELECT WeeklyRentHistory.RentAmount
FROM WeeklyRentHistory
WHERE (((forms!WeeklyRentCharge!StartDate) Between WeeklyRentHistory.EffectiveFrom And WeeklyRentHistory.EffectiveTo) And ((WeeklyRentHistory.EffectiveTo) Is Not Null) And ((WeeklyRentHistory.Lodger)=forms!WeeklyRentCharge!Lodger)) Or (((forms!WeeklyRentCharge!StartDate)>=WeeklyRentHistory.EffectiveFrom) And ((WeeklyRentHistory.EffectiveTo) Is Null) And ((WeeklyRentHistory.Lodger)=forms!WeeklyRentCharge!Lodger));

which I named in the attached database as qryGetRent. It does all the heavy lifting so all you need in the afterupdate event of the StartDate of the WeeklyRentCharge form is:

Code:

Me.Amount = DLookup("[RentAmount]", "[qryGetRent]")

Note that if you use the Date Picker you still need to move out of the StartDate field before the afterupdate is fired and you see the amount entered.

I haven't tested this thoroughly. I uploaded your modified database with this query and the afterupdate code so you can test it before installing it in your database. Please don't use this database for anything but testing or copying from. I felt like I was wearing a straight jacket working in the database so I removed some stuff you probably want.

As an aside I'd suggest considering whether your modal forms real need to be that way. It really pisses me off when an application presents me with one of those and I need to do something else to get the information the form is requesting but of course it won't let me.

maff811 · Mar 2, 2017

Thanks Steve, I'll check this out now.

I understand what you mean with the different measures - I've tried to make it fool proof and ensure all the data that is required is entered and not left blank. I'll have a look at what you've done.

Thanks again

maff811 · Mar 3, 2017

Hi Steve,

Thanks for your help. I managed to get it to do what I wanted it to do. I tied myself up in a knot there for a while but with a clear head it took me a couple of minutes.

Thanks again for your help.

DLookup function in Query (1 Viewer)

Registered User.

Attachments

Well-known member

AWF VIP

AWF VIP

AWF VIP

AWF VIP

Registered User.

Registered User.

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

AWF VIP

Registered User.

Attachments

AWF VIP

Attachments

Registered User.

Registered User.

Similar threads

Users who are viewing this thread