Access 2010 DLookup – Table name is a variable value

Topic

New Reply

Hi, I can’t find an answer to this anywhere.

I have a situation where the table name is stored as a text value in a table and this can be different for each record. The Field that contains the table name is “PREV_TABLE” and is stored in a table “TBL_PAYMENT_DUE”.

I have tried DLookup(“[ACCT_NO]”, “TBL_PAYMENT_DUE.PREV_TABLE” , “ID=” &TBL_PAYMENT_DUE.ID) which returns #Error.

If I replace the table name with one of the text values in PREV_TABLE it works fine for those records that the fixed table name applies to.

What is the correct syntax?

Thanks

Reply | Quote

Replies

I’m not sure of the context in which you are using it (in a query, in code, etc.). But this is most likely what you need (notice I removed the quotes around the tablename:

DLookup(“[ACCT_NO]”, TBL_PAYMENT_DUE.PREV_TABLE , “ID=” & TBL_PAYMENT_DUE.ID)

Reply | Quote

Many thanks – so basic and so simple I hadn’t tried this! It is within a query at the moment but may well end up in VBA.

Reply | Quote