DSum for last month

[MikeLeBen]

I need to implement a new criterion in the following DSum, it now has to only sum the values for records that have a DataDecorrenza (that's a date field) not older than last month from Now().

I.e.: today's may 24, dsum must only consider records dated april 1st and older.
As it turns June, only records dated may 1st or older will be considered.

How should I change this?

MyAvailable = Nz(DSum("ValoreRicetta", "Clienti_Ricette", _
                      "IDCliente= " & cboRicetteEsistenti.Column(0)), 0)

 

[JANR]

Perhaps something along this line:

MyAvailable = Nz(DSum("ValoreRicetta", "Clienti_Ricette", _
                      "IDCliente= " & cboRicetteEsistenti.Column(0) & _ 
                      " AND DataDecorrenza <=" & DateSerial(Year(Date), Month(Date)-1,1), 0)

JR

 

[MikeLeBen]

Beautiful.
I only had to use the format function (EU dates here), and change the operator from less than or equal to greater than or equal to (who's interested can see code below).

lastmonth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "\#mm\/dd\/yyyy\#")

MyAvailable = Nz(DSum("ValoreRicetta", "Clienti_Ricette", _
                      "IDCliente= " & cboRicetteEsistenti.Column(0) & _
                      " AND DataDecorrenza >= " & lastmonth), 0)

 

[JANR]

I only had to use the format function (EU dates here)

Good catch.

change the operator from less than or equal to greater than or equal to

That's not what you stated in the first post.

I.e.: today's may 24, dsum must only consider records dated april 1st and older

Currently your Dsum gives you the sum from 1. April to present day 25. May!

JR

 

[MikeLeBen]

Right, I typed the wrong thing but what I meant is newer