Using a DLOOKUP

[derek7467]

Afternoon!
I need to use a dlookup on a report to look up fields on another query, other than what the report uses.

How do i use the dlookup syntax?



I have a field on another query called "Average" and the query name is AverageResults. The report is called Totals.

[Xipooo]

DLookup("<FieldName1>", "<QueryName>", "<FieldName2>=<Criteria>")

FieldName1 will be the field you want to return a result from. Queryname is the name of the query you want to pull data from. FieldName2 can be any field in the query that you want to use to filter down to a specific row in your data. The criteria is whatever value you want to find out of FieldName2.

In the third parameteter, you can use AND and OR's to evaluate other criteria as well. It is essentially the "WHERE" clause of a query without the actual WHERE keyword.

DLookup("Average", "AverageResults", "ID=2 AND TimeStamp < #1/14/2014#") for example.

[derek7467]

What if i just wanted to display one field from another query, would this work?:

=DLookUp("Avg ACD Calls","SupervisorDailyHuddle - Averages")

Where Avg ACD Calls is the field from the query SupervisorDaily Huddle - Averages; do i have to put a Criteria clause in there?

[Xipooo]

What if i just wanted to display one field from another query, would this work?:

=DLookUp("Avg ACD Calls","SupervisorDailyHuddle - Averages")

Where Avg ACD Calls is the field from the query SupervisorDaily Huddle - Averages; do i have to put a Criteria clause in there?

It seems either I didn't explain it well enough or you made the same mistake I occasionally do and didn't read the whole post.

The first field IS THE FIELD OF DATA TO DISPLAY. You will get only ONE single result back.

The DLookup function takes three string arguments. The first argument is the FIELD of the query or table you want to return. The second argument is the name of the table or query you want to get the data from. The third argument is a criteria of how to narrow down which ROW of data the information is in, and it will return the first value that matches your criteria.

[derek7467]

I read the whole post; just didnt understand the third argument:
The third argument is a criteria of how to narrow down which ROW of data the information is in, and it will return the first value that matches your criteria.

Since its only one row in the data, since its an average:


I want to return the Avg ACD Calls which is column 2 row 1.

=DLookUp("Avg ACD Calls","SupervisorDailyHuddle - Averages", "Avg ACD Calls=1")???

Lastly, im then placing a text box on my report on the footer, and using the Control Source to input my dlookup?

Sorry for all the ?'s I appreciate the help.

[June7]

The WHERE CONDITION argument could be like:

=DLookup("Avg ACD Calls", "SupervisorDailyHuddle", "LoginDate=#2/2/2014#")

Is SupervisorDailyHuddle a query? Does it aggregate the calls by date?

[Xipooo]

the where condition argument could be like:

=dlookup("avg acd calls", "supervisordailyhuddle", "logindate=#2/2/2014#")

is supervisordailyhuddle a query? Does it aggregate the calls by date?

this ----^

[derek7467]

Thanks guys. testing it now. June, yes its an aggregate function, and avgs the columns by a certain date.

[derek7467]

When i use =dlookup("avg acd calls", "supervisordailyhuddle", "logindate=#2/2/2014#")

I get an #Error:

Here is how i am placing it on my Report

[June7]

Field names have spaces, try enclosing in [].

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

If you want the date criteria to be dynamic and reference the LoginDate from the footer:

=DLookup("[Avg ACD Calls]", "SupervisorDailyHuddle", "LoginDate=#" & [LoginDate] & "#")