Using DLOOKUP in MS Access?

Question

I'm trying to use DLOOKUP as a control source for an unbound field in report view. So far my code looks like this:

=DLookUp("[Client]","Client Consolidation Query",[Record]=2)

I want to look up the second record in a query, but don't want to specify any other criteria as I want the information to remain sorted by a certain field. So far I get a #NAME? error. Is what i'm trying even possible?

Answer 1

The criteria must also be a string

=DLookUp("[Client]","Client Consolidation Query","[Record]=2")

but this will only work if 'Record' is a field name, otherwise not.

One solution would be to use a compound TOP query and display the second record in a list box (shaped like a text box), where the control source is that query.

Answer 2

You need square brackets around anything with spaces...

=DLookUp("[Client]","[Client Consolidation Query]",[Record]=2)