Help with multiple criteria in DLookup Function

[Marie via AccessMonster.com]

I am using a DLookup function on a report to lookup a budget amount [Mgr
Budget Amount] in a query [qryDeptProdMgrBudget].
The DLookup function worked fine when I only had one criteria e.g. [Funding
Type] = Cash, however I now need to add additional critieria which will match
the [EmplID] field on the report with the [MgrEmplID] field in the query.
I created the expression below, however, the expression is ignoring the
second criteria.
Must be something wrong with the syntax, however, I am stumped.

Any help would be much appreciated!

=DLookUp("[Mgr Budget Amount]","qryDeptProdMgrBudget","[Funding Type]='Cash'"
And "[MgrEmplID] =" & [Report]![EmpID])

 

[Guest]

Hi Marie,

leave the And inside the " ";

=DLookUp("[Mgr Budget Amount]","qryDeptProdMgrBudget","[Funding Type] =
'Cash' And [MgrEmplID] = " & [Report]![EmpID] & "")

hope that helps,

TonyT..

 

[Marie via AccessMonster.com]

Tony,

I tried leaving the And inside the " " as you suggested, but now # Error
displays in the field on the report.
=DLookUp("[Mgr Budget Amount]","qryDeptProdMgrBudget","[Funding Type]='Cash'
And [MgrEmplID] = " & [Report].[EmpID])

Any other ideas I could try would be greatly appreciated!

Hi Marie,

leave the And inside the " ";

=DLookUp("[Mgr Budget Amount]","qryDeptProdMgrBudget","[Funding Type] =
'Cash' And [MgrEmplID] = " & [Report]![EmpID] & "")

hope that helps,

TonyT..

I am using a DLookup function on a report to lookup a budget amount [Mgr
Budget Amount] in a query [qryDeptProdMgrBudget].

[quoted text clipped - 9 lines]

=DLookUp("[Mgr Budget Amount]","qryDeptProdMgrBudget","[Funding Type]='Cash'"
And "[MgrEmplID] =" & [Report]![EmpID])

 

[John Vinson]

Tony,

I tried leaving the And inside the " " as you suggested, but now # Error
displays in the field on the report.
=DLookUp("[Mgr Budget Amount]","qryDeptProdMgrBudget","[Funding Type]='Cash'
And [MgrEmplID] = " & [Report].[EmpID])

Any other ideas I could try would be greatly appreciated!

The problem is probably the [Report]. syntax - that means nothing to
Access. Which report!? Try just [EmpID] if there is a field of that
name in the form's Recordsource.

John W. Vinson[MVP]

 

[Marie via AccessMonster.com]

John,

Thank you for your suggestion.
Yes, there is a field EmpID in the Report source. I tried removing the
[Report]. syntax. The same result (#Error) occurred.
I also tried creating a separate text box with the following
=DLookUp("[Mgr Budget Amount]","qryDeptProdMgrBudget"," [MgrEmplID] = " &
[EmpID]) with the same result.

I will try to describe the situation better.
The report is grouped on the EmpID which is displayed in the header of the
report.
If I don't include the 2nd criteria in the DLookup, the result is the first
employee's budget amounted displayed in the query is displayed for each
employee in the report.
Hope this helps explain the situation better. If not, please let me know and
I will try to provide more details.

Thanks in advance for your help!

Tony,

[quoted text clipped - 4 lines]

Any other ideas I could try would be greatly appreciated!

The problem is probably the [Report]. syntax - that means nothing to
Access. Which report!? Try just [EmpID] if there is a field of that
name in the form's Recordsource.

John W. Vinson[MVP]

 

[Marshall Barton]

Make sure that the text box is named something other than
EmpID
--
Marsh
MVP [MS Access]

John,

Thank you for your suggestion.
Yes, there is a field EmpID in the Report source. I tried removing the
[Report]. syntax. The same result (#Error) occurred.
I also tried creating a separate text box with the following
=DLookUp("[Mgr Budget Amount]","qryDeptProdMgrBudget"," [MgrEmplID] = " &
[EmpID]) with the same result.

I will try to describe the situation better.
The report is grouped on the EmpID which is displayed in the header of the
report.
If I don't include the 2nd criteria in the DLookup, the result is the first
employee's budget amounted displayed in the query is displayed for each
employee in the report.
Hope this helps explain the situation better. If not, please let me know and
I will try to provide more details.

Thanks in advance for your help!

Tony,

[quoted text clipped - 4 lines]

Any other ideas I could try would be greatly appreciated!

The problem is probably the [Report]. syntax - that means nothing to
Access. Which report!? Try just [EmpID] if there is a field of that
name in the form's Recordsource.

John W. Vinson[MVP]