Dlookup field in a table not linked to report 1

[Eprice]

Hi,
I am having problems with the syntax I guess because I only get three for everything. I am looking for a field in a table that meets two criteria. Both the table I am looking in and the table the report is linked to have the same field names. Here is what I have:

=DLookUp("[SumOfRegAud]","tbl HopaProductionCntTotals","[WeekEndingDate]=[WeekEndingDate] And [ReviewerNo]=[ReviewerNo]")

Im sure I am missing quotes but not sure where. Please help.
Thanks
Lisa

 

[jbpez]

Try:

=DLookUp("[SumOfRegAud]","tbl HopaProductionCntTotals","[WeekEndingDate]=#" & Reports!ReportName![WeekEndingDate] & "# And [ReviewerNo]=" & Reports!ReportName![ReviewerNo])

Note: try not to name your tables with spaces in them.

John Borges

 

[dhookom]

Try:
=DLookUp("[SumOfRegAud]", "tblHopaProductionCntTotals", "[WeekEndingDate]=#" & [WeekEndingDate] & "# And [ReviewerNo]=" & [ReviewerNo])
This assumes you want to filter the return of the DLookup() to the WeekEndingDate and ReviewerNo fields in the report and that ReviewerNo is numeric. If ReviewerNo is text, you will need to add text delimiters.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

A better option (if it works) is to add tblHopaProductionCntTotals to the report's record source and join the WeekEndingDate and ReviewerNo fields. You can then add the [SumOfRegAud] field to the query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Eprice]

Thanks for the help. I can't add the table to the reports record source because the detail comes from that table. Now that I know the syntax for more than one criteria I can use it with other reports if needed. I just wasn't sure about the quotes and ampersands. Thanks
Lisa