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?
dawber39Database Analyst / Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
While that could probably get flanged up, the domain aggregate functions are things to try to avoid, in general
DSum syntax is
DSum ( SomeField , SomeTableorQuery [, SomeProperlyFormattedWhereClauseWithoutWHERE] )

I take it that you want something like
DSum ( SomeField , SomeTableorQuery , SomeField > DateAdd("d", -14, Date() )
for a sum of somefield within the last 14 days.

That'd work most likely, but calculating such a thing on each row of a detail section is likely to be a performance dog
0
PatHartmanCommented:
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.
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
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()))
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nick67Commented:
Every bloody thing in DSum must be in quotes, so start there
=DSum("[SALES]","[tblSalesVsFcst-Weekly]","[WEEK]>=DateAdd("d",-32,Date()) And [WEEK]<=DateAdd("d",-6,Date())")

This looks ok on the face of it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
BUT...
Quotes within quotes are a BIG problem
"[WEEK]>=DateAdd("d",-32,Date()) And [WEEK]<=DateAdd("d",-6,Date())")

ANd I don't know if that can be remedies without resorting to VBA in one way or another, or tweaking the query
You can try single quotes in the DateAdd

=DSum("[SALES]","[tblSalesVsFcst-Weekly]","[WEEK]>=DateAdd('d',-32,Date()) And [WEEK]<=DateAdd('d',-6,Date())")
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
Comes back with "invalid syntax - must be missing an operand"
0
Nick67Commented:
DSum will take a table or a query for a domain
Soo...
Create a query
Select Sales, Date()-6 as Sixback, Date()-32 as ThirtyTwoBack from tblSalesVsFcst-Weekly and save it as
qrySaleswithBack

Then

=DSum("[SALES]","[qrySaleswithBack]","[WEEK]>=ThirtyTwoBack And [WEEK]<=SixBack")

May work

Date()-6 and Date()-32 may work straight up
They may need to be the DateAdd though

Select Sales, DateAdd("d",-6,Date()) as Sixback, DateAdd("d",-32,Date())  as ThirtyTwoBack from tblSalesVsFcst-Weekly
0
Nick67Commented:
But, since you need to go that far then the query can be

Select Sum(Sales) as SumSales from tblSalesVsFcst-Weekly WHERE [WEEK]>=DateAdd("d",-32,Date()) And [WEEK]<=DateAdd("d",-6,Date())"   and save it as
qrySaleswithBack and call it a day.

That only returns a single record so then the control source can use the poorly named DFirst() function
DFirst("sumSales","qrySalesWithBack")

poorly named DFirst() function
DFirst() and DLast() return a random record from a query/table
Not what you'd expect given their names!
use them only if you need one record from a one record table/query!
0
Nick67Commented:
The syntax for the query suggestion in A#40692281 isn't quite right.
My Bad.
You'll need [Week] in there, and then, because of that, you'll probably need a Group By clause.
That's a little too complex to air-code -- but I think you've got enough to get the idea!
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
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()))
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
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
0
Nick67Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.