Need help with DLookup

[April15Hater]

Hey guys-

I am treading on unchartered waters here I have never used the dlookup function and reports are generally a problem area for me.

But I'm trying to implement dlookup into a textbox in the detail section of a report and I can't seem to get it to work right. But when I run it, it is coming up with a value input window for FunctionID. I've searched all around and everything seems to be right syntactically. I posted my code below.

=DLookUp("[tblContractorFunction].[FunctionCost]","tblContractorFunction","tblContractorFunction.FunctionID = " & [Reports]![rptTimesheet]![FunctionID] & "AND tblContractorFunction.FunctionType = Aerial")

Thanks,

Joe

 

[boblarson]

Syntax:
=DLookup ("YourFIELDNameHere", "YourTableNameHere", "AnyCriteriaHere")

so
=DLookUp("[FunctionCost]","tblContractorFunction","[FunctionID] = " & [Reports]![rptTimesheet]![FunctionID] & " AND [FunctionType] = 'Aerial'")

 

[April15Hater]

Thanks Bob. Well I do have the syntax right. I tried your code but it did the same thing: still asking for a FunctionID value. I triple-checked to see if FunctionID was in the controlsource and it is.

 

[boblarson]

Well I do have the syntax right.

You didn't have the syntax right. You refer to the field, not the table.field in the field spot as you are already telling it where to look when you put your table in the table spot (a query can go there too).

I tried your code but it did the same thing: still asking for a FunctionID value. I triple-checked to see if FunctionID was in the controlsource and it is.

It might not like coming from a report object. Also, is FunctionID numeric or text?

 

[April15Hater]

Sorry, when I said syntax, i meant I had the arguments in the right place: =DLookup ("YourFIELDNameHere", "YourTableNameHere", "AnyCriteriaHere")

The FunctionID is numeric. You gave me a hunch though. FunctionID isn't actually an object on the report but it is a field in the controlsource. Will [Reports]![rptTimesheet]![FunctionID] pull from the controlsource?

 

[boblarson]

The FunctionID is numeric. You gave me a hunch though. FunctionID isn't actually an object on the report but it is a field in the controlsource. Will [Reports]![rptTimesheet]![FunctionID] pull from the controlsource?

Actually, I don't know that it will. Try putting it on the report (as a hidden object) and see if that helps.

 

[April15Hater]

That, my friend, did the trick! Thanks for all of your help, you truly are a life (and sanity) saver!

 

[boblarson]

(and sanity) saver!

Well, at least you still have some - I think I lost mine years ago.