Link to home
Start Free TrialLog in
Avatar of dawber39
dawber39Flag for United States of America

asked on

MS Access Syntax Possibilities?

Has anyone has actually ever used the DateAdd function within the DSUM Function on a report in Access, and if so - how would that syntax go?
SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of dawber39

ASKER

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()))
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Comes back with "invalid syntax - must be missing an operand"
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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