Help with DLookup Criteria

  • Thread starter MarieM via AccessMonster.com
  • Start date
M

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
 
F

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

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

Tom Lake

MarieM via AccessMonster.com said:
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
 
M

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


Tom said:
[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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top