Help with DLookup Criteria

[MarieM via AccessMonster.com]

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

 

[fredg]

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

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] & "'")

 

[MarieM via AccessMonster.com]

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 - 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] & "'")

 

[Tom Lake]

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?

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

 

[MarieM via AccessMonster.com]

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 - 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