DLookUp in a Query

[jerryh]

Good afternoon,

I have a question regarding putting together a query with a DLookUp field. Here is what I'm trying to accomplish:

I have a set of vendors that I review regularly and enter results in a new record every time I do a new review. What I'd like to do is set up a query that compares the most recent review results against the last results to determine if the results or improving, getting worse, or staying the same. I think the DLookUp field would be ideal to do it, but I can't seem to figure out the expression should be....

Here is a run down of what's in my database:
Vendor Table - Listing all Vendors we use and scored based on what we use them for.
Vendor Oversight Table - Pulls for the Vendor name from the vendor table and I input the results of the review
Query - Pulled from Vendor Table - Vendor Name, Vendor Score
- Pulled from Vendor Oversight Table - Date Completed, Vendor Oversight Results
- Calculated - Residual value between the vendor score and vendor oversight results
- DLookUp for Last Vendor Oversight Results - DLookUp("Vendor Oversight Results","Vendor Oversight Table","???")
- Calculated - Resideual value between the vendor score and the DLookUp results

What is the expression to pull the vendor oversight results for the last review done for that vendor???

Thank you in advance for any and all advice.

[June7]

This is actually more difficult than you think. What determines the 'last' results - a date?

DLookup("[Vendor Oversight Results]", "[Vendor Oversight Table]", "VendorID=" & [VendorID] & " AND [datefield]=" & DMax("DateField", "[Vendor Oversight Table]", "VendorID=" & [VendorID]))

A TOP N nested query should be able to return the complete record. Review http://allenbrowne.com/subquery-01.html#TopN


Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention.

[jerryh]

Thank you so much for the advice. I will go through and remove the space and replace with underscores.

To answer your question, yes, the last result is by date.

[jerryh]

So I put the expression in and I'm getting the following error:

Syntax Error (Missing Operator) in query expression 'vendor=COCC'.

"COCC" is the name of one of the vendors. And it comes up for every record in the query, leaving the same vendor name in the error message.

[June7]

The expression assumes VendorID is a number value. If you use text, then the parameter must be enclosed in apostrophes.

Also, date/time parameters require # delimiter, which I forgot in my example.

DLookup("[Vendor Oversight Results]", "[Vendor Oversight Table]", "VendorID='" & [VendorID] & "' AND [datefield]=#" & DMax("DateField", "[Vendor Oversight Table]", "VendorID='" & [VendorID] & "'") & "#")

[jerryh]

Hmm... Still getting a syntax error. Here's what I did:

I switched it from the Vendor's name to the Vendor ID number to get a numerical value, as per your first expression, and took out the apostrophes that you had entered. However, I'm getting an error in the cells: Syntax error in date in query expression 'VendorID=24 AND [datecompleted]=#'

[June7]

You changed the VendorID in both domain aggregate expressions? Post the full expression for analysis.

Did you try the TOP N approach?

Another approach might be to do an aggregate query object that returns the max date for each vendor then another query that joins the aggregate query back to the original table with a compound linking on VendorID and datecompleted fields.

[jerryh]

Here is the expression, yes I removed both:

DLookUp("[VendorRiskRating]","[Vendor_Oversight]","VendorID=" & [VendorID] & " AND [datefield]=#" & DMax("Datefield","[Vendor_Oversight]","VendorID=" & [VendorID] & "") & "#")

I've heard of the TOP N, and I saw you posted a link. I will take a look. I'll also play around with creating the two queries. Please let me know if you spot an error in my expression.

Thank you again for all your help.

[June7]

Is datefield the actual name of your field? I used that in my example because I didn't notice the field name in post 1 which I see now shows DateCompleted. You show [datecompleted] in post 6.

[jerryh]

It is "DateCompleted". I changed your "DateField" to "DateCompleted" in the expression.

[June7]

Then I don't see anything wrong with the syntax.

If you still have issue, next step is to provide the db. Follow instructions at bottom of my post.

[jerryh]

So reading back over our thread, and looking over my database, I realized that I had the right field name, but wrong table name. While that table exists... It doesn't have those fields. Now that I corrected that error, your formula works perfectly!

Thank you so much for helping me on this.