Dsum formula results for quarters in previous year

[UltimateNeo]

Hello, I am having trouble with this formula not giving me the q2 results of last year. It still give me the current year results

=DSum("[QTY]","[InspectionsT]","[TaskID]=3 And Datepart('q',Dateadd('yyyy', -1, [DateCompleted]))=2")

Any help would be greatly appreciated

 

[Gasman]

Break it down and test each part.

Perhaps also look up the syntax?

DateAdd function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

DatePart function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[UltimateNeo]

The part that is not working is this part =DateAdd('yyyy',-1,[DateCompleted]) i get #name?

 

[Gasman]

So did you look up the syntax?

 

[UltimateNeo]

yeah it looked ok and the rest of the formula has been working with datecompleted field.

 

[Gasman]

It did not look OK to me. What country are you from?
Perhaps follow the examples?

 

[UltimateNeo]

I have tried examples for example =DateAdd("yyyy",-1,[DateCompleted]) still does not like it. I have not seen any other examples with different syntax.

 

[Gasman]

So in the immediate window what does
? =DateAdd("yyyy",-1,[DateCompleted])
show you?
Does datecompleted always have a value?

 

[UltimateNeo]

I solved it wit this =DSum("[QTY]","[InspectionsT]","[TaskID] = 3 And Datepart('q',[DateCompleted]) = 2 and Datepart('yyyy',[DateCompleted]) = Year(Date()) - 1") splitting the year and quarter

 

[Gasman]

What are you going to do when you want the next quarter?

 

[UltimateNeo]

I have 8 textboxes each with different quarter in them. 4 for last year and 4 for this year on a form. As they are just calculated fields from the data in the table. I couldn't think of any other way to do it

 

[Gasman]

If that is what you need to see each time, then that will work.

 

[Pat Hartman]

I have 8 textboxes each with different quarter in them. 4 for last year and 4 for this year on a form. As they are just calculated fields from the data in the table. I couldn't think of any other way to do it

There are solutions that won't require code changes every year if you are interested.

 

[ebs17]

Instead of creating a fireworks display with DSum, it is better to calculate everything in a database in one query.
Approach:

SELECT
   Year(DateCompleted) AS QYear,
   DatePart('q', DateCompleted) AS Quarter,
   SUM(QTY) AS SumQty
FROM
   InspectionsT
WHERE
   DateCompleted BETWEEN DateSerial(Year(Date()) - 1, 1, 1)
      AND
   DateSerial(Year(Date()), 12, 31)
GROUP BY
   Year(DateCompleted),
   DatePart('q', DateCompleted)

 

[Galaxiom]

Instead of creating a fireworks display with DSum, it is better to calculate everything in a database in one query.

Take notice of this solution. It not only does the whole job in a single query, it also allows an index on DateCompleted to be used. This will be massively better performing than any query (including a DSum) that has to apply functions to every record in the table before selecting.


It is important to understand and apply the principles of SARGable queries.