Help with DLookup Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MarieM - 24 Jan 2007 02:58 GMT

I have a report which displays Expense information totaled by month for each
employee. The report is grouped by employee.
I would like to display budget information which is calculated in a separate
query for each employee.
Through the use of the following DLookup statement I am able to pull the
information from the query.
However, the first record from the query, first employee's result is
displayed for each employee on the report.

=DLookUp("[%BudgetSpent]","qryEmpBudgetSummary","[GP Member Emplid]=[GP
Member Emplid]")
where [%BudgetSpent] is the field from the query
[qryEmpBudgetSummary] is the query
"[GP Member EmplID]=[GP Member EmplID]" is the criteria

How can I change the DLookup criteria to ensure it is pulling the correct
employee's information from the query?
I changed the criteria to use the full syntax of the report field, but that
didn't work either - it actually does not display anything when I use the
full syntax.

Any help would be greatly appreciated!
Marie

Reply to this Message

fredg - 24 Jan 2007 03:25 GMT

> I have a report which displays Expense information totaled by month for each
> employee. The report is grouped by employee.
[quoted text clipped - 19 lines]
> Any help would be greatly appreciated!
> Marie

You must concatenate the criteria value into the string.

Is [GP Member Emplid] a Number datatype field?

=DLookUp("[%BudgetSpent]","qryEmpBudgetSummary","[GP Member Emplid]= "
& [GP Member Emplid])

If it is a Text datatype, then use:

=DLookUp("[%BudgetSpent]", "qryEmpBudgetSummary", "[GP Member Emplid]=
'" & [GP Member Emplid] & "'")

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

MarieM - 24 Jan 2007 03:59 GMT

Fred,

Thanks for your suggestion. However, when I concatenated the criteria value
to the string as you suggested, the report now displays #Error.
I tried pasting the expression in the immediate window and received "External
name not defined" message. Should I be declaring something as Public?

By the way, [GP Memberl EmplID] is defined a text data type.
Is there something else I can try?

Thank you, Marie

>> I have a report which displays Expense information totaled by month for each
>> employee. The report is grouped by employee.
[quoted text clipped - 13 lines]
>=DLookUp("[%BudgetSpent]", "qryEmpBudgetSummary", "[GP Member Emplid]=
>'" & [GP Member Emplid] & "'")

Reply to this Message

Tom Lake - 24 Jan 2007 04:27 GMT

> Fred,
>
[quoted text clipped - 5 lines]
> By the way, [GP Memberl EmplID] is defined a text data type.
> Is there something else I can try?

Since it's a text type you have to add either quotation marks or apostrophes.
I've added apostrophes below:

=DLookUp("[%BudgetSpent]","qryEmpBudgetSummary","[GP Member Emplid]= '"
& [GP Member Emplid] & "'")

Tom Lake

Reply to this Message

MarieM - 24 Jan 2007 14:35 GMT

Tom,

Thanks for your suggestion. However, when I concatenated the criteria value
to the string as you suggested, the report now does not display any result.
With my original criteria I had the first employee's information displayed
for each employee.

By the way, [GP Memberl EmplID] is defined a text data type.
Is there something else I can try?

Thank you, Marie

>> Fred,
>>
[quoted text clipped - 9 lines]
>
>Tom Lake

Reply to this Message