MS Access Syntax Possibilities?

To continue Nick's explanation, the substitute for DSUM() is frequently to create a totals query and join to it.  This process allows the query engine to optimize the process.  The substitute for DLookup() is usually a Left Join to the lookup table.  There are instances where you don't have options but we don't know enough about what you are doing to tell you unequivocally to use a totals query and join to it.

I understand what you are saying - but it is not my place to push in the correct direction - Here is the actual line we are trying to get to work and it keeps coming up  with an error - at first it couldn't find the table because of the hyphen I figure - Im close - but I don't think this syntax is correct

=DSum([SALES],"[tblSalesVsFcst-Weekly]",[WEEK]>=DateAdd("d",-32,Date()) And [WEEK]<=DateAdd("d",-6,Date()))

Comes back with "invalid syntax - must be missing an operand"

It returns nothing on the report. Can I do something like this in the Record SOurce? I keep getting an error from that of below looking for a parameter  for "Select SUM(Sales)" - and I don't understand it. The query listed is the source of the report

Select SUM(Sales)
From qryPlanner-ProdMethod-WkREPORT
WHERE
WkBeginDate>=DateAdd("d",-32,Date()) And WkBeginDate<=DateAdd("d",-6,Date()))

Im at least going in the right direction - I am able to write a query that produces the results I want - so I will just maybe take that SQL - and put it in the control source for the text box on the report. I'm sure I'll be back with more on this

Thanks people

I keep getting an error from that of below looking for a parameter  for "Select SUM(Sales)"
If qryPlanner-ProdMethod-WkREPORT doesn't have a column with Sum([Sales]) in it, then you are in the weeds for sure
And even if it does, the column will have an alias [Expr1] if you didn't give it one, or perhaps MS gave it SumOfSales, or you gave it a name.
Whatever the case

Select WhateverAliasTheSumOfSalesColumnHasInTheQuery
 From qryPlanner-ProdMethod-WkREPORT
 WHERE
 WkBeginDate>=DateAdd("d",-32,Date()) And WkBeginDate<=DateAdd("d",-6,Date()))
is the required syntax.

I am able to write a query that produces the results I want
Great!
Reports are read-only
Now, can you join your new query into the Report's RecordSource?
That's the end idea anyway
Then its just another field and you can give the control the fieldname as a controlsource