DLookup Multiple Criteria Help Needed (1 Viewer)

[MarcusAD79]

Hi,
I have been trying for hours to get the following DLookup to work. I've read forum after forum and help pages to no avail.
Problem :
DLookUp("[DIFFRERENCE]","qrySubUsage","MeterID=" & [Sub1MeterID] And "CURRENT_READ_DATE=#" & [CURRENT_READ_DATE] & "#")

Source Query "qrySubUsage" has the following fields:
MeterID | PREVIOUS_READ_DATE | PREVIOUS_READ | CURRENT_READ | CURRENT_READ_DATE | DIFFERENCE | (There are other fields that aren't used)
qrySubUsage : DIFFERENCE is subtracting the Previous read amount from the Current. This works.

Where the problem is, some of the meters have sub-meters; the power they use is recorded in the parent meter.
I am trying to create a query that will display the DIFFERENCE from the sub meter in a field for the parent meter for each date.
The "qryUsage" is a copy of "qrySubUsage" with extra fields:
MeterID | PREVIOUS_READ_DATE | PREVIOUS_READ | CURRENT_READ | CURRENT_READ_DATE | DIFFERENCE | Sub1MeterID | Sub1Usage | ByMeter | ByDate

Sub1Usage = DLookUp("[DIFFRERENCE]","qrySubUsage","MeterID=" & [Sub1MeterID] And "CURRENT_READ_DATE=#" & [CURRENT_READ_DATE] & "#")

I also tried to break it up into two DLookups to try to figure out the problem, but neither work correctly.
ByDate: DLookUp("[DIFFRERENCE]","qrySubUsage","CURRENT_READ_DATE=#" & [CURRENT_READ_DATE] & "#") = Does pull DIFFERENCE, but only for the first four dates and then just repeats them.
ByMeter: DLookUp("DIFFRERENCE","qrySubUsage","MeterID=" & [Sub1MeterID]) = Pulls the first DIFFENCE and repeats it.

Any help would be great.
Thank you!

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

Here are just a couple of tips for you:

1. Feel free to ask any question you like, however basic you may feel it is, or even if it has been answered before. Our expert members thrive on helping you out!

2. If you prefer a dark theme to the forums, just go to the bottom left of this forum and click "Default style". You will then see a selection of themes to choose from. I like Shades of Blue.

3. If you like any of the answers you get, feel free to click the "Like" link on the bottom right hand corner of the post. If you hover over the Like link, you can even choose the type of smiley.

Above all, hang around here, have fun, learn stuff and join in.

 

[theDBguy]

Hi. Welcome to AWF!

Since you're asking a question, rather than introducing yourself, I moved your thread to the Queries Forum out of the Introduce Yourself Forum.

Give this one a try:

DLookUp("[DIFFRERENCE]","qrySubUsage","MeterID=" & [Sub1MeterID] & " And CURRENT_READ_DATE=#" & [CURRENT_READ_DATE] & "#")

Hope that helps...

 

[plog]

The good news is this doesn't involve a Dlookup. They have no place in queries.

What you need is to JOIN qrySubUsage to the data sources in the query you are currently building. Bring it in to the query Designer, connect it appropriately and then you will have access to the values in qrySubUsage without doing a Dlookup.

 

[MarcusAD79]

theDBguy: That worked!!!! (And sorry for missing where I was posting at first)
I want to make sure I understand what I did wrong.
There has to be a & between Criteria1 and Criteria2 and I had AND " before the Criteria2, it needs to be reversed, like this " AND .
While it does work, is there a good way to get rid of the #Error for the records that do not have a SubMeter? The following works, just wondering if there is a better way:
IIF(IsNumeric([Sub1MeterID]) OR IsNumeric([CURRENT_READ_DATE]),(DLookUp("[DIFFRERENCE]" , "qrySubUsage" , "MeterID=" & [Sub1MeterID] & " And CURRENT_READ_DATE=#" & [CURRENT_READ_DATE] & "#")),"")

Thank you so much, I've gotten such a headache trying to fix this and had no idea what I was going to do if I couldn't get it to work.


plog: I've not used JOIN before. I will look into it. If you happen to know of a good place to start learning it, please let me know.

 

[theDBguy]

While it does work, is there a good way to get rid of the #Error for the records that do not have a SubMeter?

Hi. Try it this way.

DLookUp("[DIFFRERENCE]","qrySubUsage","MeterID=" & Nz([Sub1MeterID],0) & " And CURRENT_READ_DATE=#" & [CURRENT_READ_DATE] & "#")

 

[plog]

I would start here:

SQL Tutorial

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[MarcusAD79]

theDBguy: The Nz( ) worked. Thanks!

plog: Wow, I remember using that site back in 2000's for HTML4 I'll start using it again. Thank you!

 

[theDBguy]

theDBguy: The Nz( ) worked. Thanks!

You're welcome. Good luck with your project.