DLookup Syntax 1

[Rjc8513]

I'm attempting to use DLookup in a text box on a report and keep getting "#Error" returned. Can someone help with the proper syntax?

="Week No " & DLookup("[WEEK]","[tbl DATES]","[YRDAYWK]=" & "[tbl EMPLOYEE DATA].[YRDAYWK]")

Thanks.

 

[dhookom]

Your syntax is the same as
[tt][blue]="Week No " & DLookup("[WEEK]","[tbl DATES]","[YRDAYWK]=[tbl EMPLOYEE DATA].[YRDAYWK]")[/blue][/tt]

If YRDAYWK is a numeric field in your report's record source, then try:
[tt][blue]="Week No " & DLookup("[WEEK]","[tbl DATES]","[YRDAYWK]=" & [YRDAYWK])[/blue][/tt]

If it is text, try:
[tt][blue]="Week No " & DLookup("[WEEK]","[tbl DATES]","[YRDAYWK]='" & [YRDAYWK] & "'")[/blue][/tt]


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]

 

[Rjc8513]

Thanks for the response Duane but that didn't work.

"YRDAYWK" is text and is not a field in the report's record source.

 

[dhookom]

Where do you expect the value of YRDAYWK to come from?

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]

 

[Rjc8513]

The report's record source is a crosstab query. The field "YRDAYWK" is a field in the table that supplies the crosstab query.

I want the lookup to find the corresponding value in the "YRDAYWK" field of the "tbl DATES" table.

 

[dhookom]

If YRDAYWK is not in the report's record source then where does the value come from that you want to "lookup to find the corresponding value in the "YRDAYWK" field of the "tbl DATES" table"?

Can you add "tbl Dates" to your crosstab 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]

 

[Rjc8513]

The "tbl EMPLOYEE DATA" table has a "YRDAYWK" field and the value of the field is identical for every record in the table.

I want to use that field value as the criteria for the lookup to find the corresponding record in the
"[tbl DATES].[YRDAYWK]" field to return the "WEEK" field value.

Hope this makes sense.

 

[dhookom]

Create a query

SELECT WEEK
FROM [tbl EMPLOYEE DATA] JOIN [tbl DATES] ON  [tbl EMPLOYEE DATA].YRDAYWK = [tbl DATES].YRDAYWK
GROUP BY WEEK;

Then add this query which should return a single record to the record source of your report.

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]

 

[Rjc8513]

That did it, Duane!

Thanks so much for your help and your patience.