Dsum for the month, but not all years? (1 Viewer)

[vidus]

Hey guys,

Having a bit of an issue with this dsum. I used to have this code with a table that contained records for that year only. Now I am combining all years into one table. My dsum code on my form is returning a value for that month as normal but now it includes the month for every year.

For example it will add up june 2010 but also add june 2011.

Ive played around with it a bunch but cant get it to only select the current month AND year... probably really simple but its not coming to me. Below is the dsum:

=DSum("[Value]","Jobs","Month([Date]) = " & Month([Date]))

Thanks all!

 

[SOS]

use this instead:

=DSum("[Value]","Jobs","[Date] Between DateSerial(Year(Date()), Month(Date()), 1) And DateSerial(Year(Date()), Month(Date())+1, 0))

That should get you everything for the current month and year.

 

[SOS]

(and you should not use DATE as a field name as that is an Access Reserved Word)

 

[vidus]

use this instead:

=DSum("[Value]","Jobs","[Date] Between DateSerial(Year(Date()), Month(Date()), 1) And DateSerial(Year(Date()), Month(Date())+1, 0))

That should get you everything for the current month and year.

Thanks I have changed the field to QuoteDate instead. Which "date" specify the field now? I can see why it should not be "date" now...

Is this correct?

=DSum("[Value]","Jobs","[QuoteDate] Between DateSerial(Year(Date()), Month(Date()), 1) And DateSerial(Year(Date()), Month(Date())+1, 0))

This is giving me an invalid string error.

 

[SOS]

Missing a quote:

=DSum("[Value]","Jobs","[QuoteDate] Between DateSerial(Year(Date()), Month(Date()), 1) And DateSerial(Year(Date()), Month(Date())+1, 0)")

 

[vidus]

Missing a quote:

=DSum("[Value]","Jobs","[QuoteDate] Between DateSerial(Year(Date()), Month(Date()), 1) And DateSerial(Year(Date()), Month(Date())+1, 0)")

Unfortunately this is returning #Error... I cant figure it out