I need help understanding DateSerial()

[RZ15]

Hi,

I have a query that calculates the sum of branch transfers for the current quarter. Here is my Quarter-to-Date field:

QTD-In: Sum(CCur(IIf([TAT]="TRN" And [INVCDAT] Between DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+1,1) And DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+4,0),[LISPRI]*[dbo_INIDP]![ORDQTY],0)))

Now, I need to make sure I understand the DateSerials, which I think I do but I'd rather be safe than sorry.

For DateSerial(Year(Date()), Int((Month(Date())-1)/3)*3+1,1):
-Year(Date()) takes the current year.

- Int((Month(Date())-1)/3)*3+1 gets the first month of the current quarter. If it's the first quarter it will always be 1. If it's the second quarter it will always be 4. Third quarter will be 7 and fourth quarter will be 10.

- ,1 ) will return the first day of the month.

So the above DateSerial will ALWAYS return the first day of the CURRENT quarter.

For DateSerial(Year(Date()), Int(Month(Date())-1/3)*3+4, 0)
-Year(Date()) takes the current year.

- Int((Month(Date())-1/3)*3+4 gets the first month of the NEXT quarter. If it's the first quarter it will always be 4. If it's the second quarter it will always be 7. Third quarter will be 10 and fourth quarter will be 13.

- , 0) will make the function return the last day of the previous month.

So the above DateSerial will ALWAYS return the last day of the CURRENT quarter.

So is my understanding correct?

Thanks in advance,
Raza

[FishVal]

DateSerial(Year(Date()), Int((Month(Date())-1)/3)*3+1,0):
So the above DateSerial will ALWAYS return the last day of the CURRENT quarter.
So is my understanding correct?

Hi, Raza. Pretty looks like your understanding is correct. Nice code logic BTW. :)

[RZ15]

Thanks for taking the time to verify that. Better I know now than when i send it off to 9 offices.

[nico5038]

Perhaps worthwhile to test or this:

QTD-In: Sum(CCur(IIf([TAT]="TRN" And format([INVCDAT],"yyyyq") = Format(Date(),"yyyyq)),[LISPRI]*[dbo_INIDP]![ORDQTY],0)))

Performs as fast as your solution.

Nic;o)