Dlookup function

[grogmi1]

Hello,

I am looking for a little help. I have a report which works fine but I want to pull in data from a different table that the report is not based on.

The report is based on a query named MaintenanceQRY and it has a field in there named 'ItemUsed'. Like I say the report works fine and displays the information I need but it would be helpful if it could also show the Item Code of 'ItemUsed' which is stored in another table named 'ProductsTBL'. The field within that table is named 'SageCode'. I was wondering if I could add a textbox to my report and add a Dlookup function to bring the 'SageCode' information in but I haven't got a clue where to begin doing this. Any help or advice would be much appreciated

 

[Minty]

You can use a Dlookup but they are slow compared to bringing it in to your query for the report.

In your query add the Products table and create a join between the name and the matching field in your existing query, then add the SageCode to your query. Now you can use it like any other field.

If you don't always have a matching item in products use a left join to only bring in the info if it's there.

 

[RuralGuy]

Could you add the ProductsTBL to the query joined on the SageCode field? That would be the cleanest approach. Otherwise, a DLookup would work if you wanted to go that way.

 

[grogmi1]

Thankyou both for your quick responses. I take on board that the Dlookup may be slower but could you give me help with this (how would I write this in a textbox on my report)

 

[Minty]

The syntax is DLookup("TheField","TheTableOrQueryName","The Criteria") There is a detailed explanation here; http://allenbrowne.com/casu-07.html

If you can't get it to work from that then post back what you have tried.

 

[grogmi1]

Thanks again Minty. I've been dragged into a meeting so probably wont get a chance to try this today. Ill message back once I've tried it and let you know either way. Thanks so much for your quick responses