Syntax Error in Query Expression

[Deggsy]

Help! I have a query designed to compile a list of training events with a parameter prompt for the Region CMB (see below):

SELECT Training_Events.Title, Training_Events.[Start Date], Training_Events.[Start Time], Training_Events.[End Time], Training_Events.Location, Training_Events.Description, Training_Events.Trainer, DLookUp("[Regional Operations Director Code]","Stores","[Cost Centre]=" & [Location]) AS Expr1
FROM Training_Events
WHERE (((DLookUp("[Regional Operations Director Code]","Stores","[Cost Centre]=" & [Location]))=[Forms]![Show_Reports]![Region_CMB]));

There are two tables involved, Training Events and Stores (Design details below). The query expression was designed to take the appropriate region code from the Stores table, under the Regional Operations Director Code field. The query was attached to a report. Whether I run the query via the report or directly, I get the same syntax error message (see below) which appears to be related to the Cost Centre. Now, where do I begin to go to find the source of the error and correct it? Is it possible that it is new data in one of the tables which is incompatible with the rest?
It seems to run the query (when I trigger it directly), but the error message still appears (in fact it seems to appear periodically after I click OK on it!)

[pbaldy]

For starters, I'd expect the two tables to be joined in the query rather than using DLookup() to get values from the second. The DLookup()'s will really slow the query down. Have you tried that?

[Deggsy]

For starters, I'd expect the two tables to be joined in the query rather than using DLookup() to get values from the second. The DLookup()'s will really slow the query down. Have you tried that?

That's a good idea - how would that look, given the information in the query (sorry, I'm not an expert and someone else created this nightmare of a database that other people are expecting me to sort out for them)

[pbaldy]

Start a new query, add both tables to the grid. If the join line isn't already added, click and drag between the appropriate fields (the fields in your DLookup criteria). Then you can add fields from both tables to the field grid below.

[Deggsy]

Start a new query, add both tables to the grid. If the join line isn't already added, click and drag between the appropriate fields (the fields in your DLookup criteria). Then you can add fields from both tables to the field grid below.

Thanks, I'll give that a try, though I think the fields that should be joined are for some reason of different types, and I'm afraid to change them because they'll probably affect other queries, forms and whatnot. Still, that's my worry, not yours, thanks again for the advice :-)

[pbaldy]

No problem, post back if you're still stuck. There are ways around the data type problem.