Dlookup criteria problem

[CarlyS]

Hello,
I am trying to use Dlookup in an unbound text box on a report and I am having trouble specifying criteria. I have the following in the control source:

=DLookUp("[ObjectiveSummarized]","ObjectiveSummarizedTable","[Ax1Label] = '" & Tables!ObjectiveSummarizedTable!ObjectiveNumber & "' ")

I want to display the field ObjectiveSummarized for each objective label on the report (Ax1Label). Ax1Label (on report) is related to ObjectiveNumber in ObjectiveSummarizedTable. Can anyone see what I've done wrong?

I get the first objective definition in the table rather than the one related to the Ax1Label. Thanks to anyone who can give this a look.

Carly

 

[pbaldy]

Try:

=DLookUp("[ObjectiveSummarized]","ObjectiveSummarizedTable","[Ax1Label] = '" & ObjectiveNumber & "'")

You want to refer to the field from the report's source, rather than the table.

 

[CarlyS]

Paul, Thank you so much for your reply. That prompts me for a parameter value. The issue is that Ax1Label is the field in the report. ObjectiveNumber is in the table whose values I am looking up. Does that make sense?

Carly

 

[pbaldy]

Sure; I was guessing at the fieldname. Try:

=DLookUp("[ObjectiveSummarized]","ObjectiveSummarizedTable","[Ax1Label] = '" & Ax1Label & "'")

 

[CarlyS]

I think I didn't explain that well...

I have ObjectiveNumber stored as data on a daily data entry basis. When I print an annual report I would like the report to lookup the actual summary of that objective in the ObjectivesSummarized table.

ObjectiveSummarizedTable is like this:

ObjectiveNumber---ObjectiveSummarized
1-------------Wash hands with 100% accuracy and no prompts
2-------------Tie shoes with 80% independence on 3 out of 4 trials


When I collect data on these objectives I only store the ObjectiveNumber to keep the table size down to a minimum. The ObjectiveNumber is stored as Ax1Label in the DataTable. The report then reports this:

Ax1Label: 2
Date---Ax1Data
1/1----D
1/2----A
1/3----A

Next to the group name I would like to say what the definition on Ax1Label is by looking that information up in the ObjectiveSummarized table. I want the report field to lookup the definition of an objective (in field called "ObjectiveSummarized) in a table called "ObjectiveSummarizedTable" when the "Ax1Label" field in the report matches the "ObjectiveNumber" field in the "ObjectiveSummarizedTable." I hope that is more clear???

It would then look like this:

Ax1Label: 2--Tie shoes with 80% independence on 3 out of 4 trials
Date---Ax1Data
1/1----D
1/2----A
1/3----A


Thank you so much for your patience.
Carly

 

[CarlyS]

Nevermind!

I got it working with:
=DLookUp("[ObjectiveSummarized]","ObjectiveSummarizedTable","ObjectiveSummarizedTable!ObjectiveNumber = " & [Ax1Label])

For anyone having a similar problem I hope this helps. I guess because Ax1Label is a number field my quotes were the problem.

Thanks for all the help!