Sum by current month (1 Viewer)

[jonwentz]

Hello all,

i have a form with 2 fields [edate] and [qty].

what i am trying to do is sum [qyt] by month

i have in the footer for a text box that sums [qty] =sum([qyt])


now i need it to be able to give me the sum of the current month


can someone let me know how to do this

thanks in advance

 

[vengsiva]

Sum qty for current month

Hello
Have you tried this
=nz(DSum("qty","YourTableName","month(edate)=Month(Date())"),0)
vengsiva

 

[jonwentz]

vengsiva

It worked great, thank you

but i will not update untill you put data in the next record

would you happen to know how to get it to update when you go to the next field.

thanks again

 

[vengsiva]

To update suqty

hello
Instead of putting the value in the data property of txtbox at the footer you may assign it via vbcode in the after update event of the field qty thus

Private Sub Qty_AfterUpdate()
Me.Txtsumqty = Nz(DSum("qty", "yourtablename", "month(edate)=Month(Date()) and year(edate)=year(Date()) "), 0)
End Sub

Incidentally i have added year criteria also
I have not tested this code
vengsiva