DSum with empty field

[edo janssen]

I have a form on which plots are displayed by their owner. When you change a plot it gets an enddate and a new plot is created with the new data. On the form I want to show the state from January the 1st of the past three years. I do this with DSum. However, if a plot has no end date it is not included in the calculation.

As you can see in the attachment, on 1-1-2010 there are 145,385 hectare, on 1-1-2011 there are 145,385 hectare and on 1-1-2012 there are 165,465 hectare. On the form the totals of 2010 and 2011 are displayed but the total of 2012 not.

    =DSum("[TotaalHectare]";"qryPerceelEigenaar2";"[Eigenaar_id]='" & Formulieren!frmPerceelEigenaar!Klant_id & "' And [Wijzigingsdatum]< #" & Formulieren!frmPerceelEigenaar!Begindatum0 & "# And [Einddatum]> #" & Formulieren!frmPerceelEigenaar!Einddatum0 & "#")

Is there a way to add plots together? Note: it is possible that in one year some plot have an enddate and some have not, for example if there has been no change to the plot.

Who can help me out?

 

[Severin]

=DSum("[TotaalHectare]";"qryPerceelEigenaar2";"[Eigenaar_id]='" & Formulieren!frmPerceelEigenaar!Klant_id & "' And [Wijzigingsdatum]< #" & Formulieren!frmPerceelEigenaar!Begindatum0 & "# And ([Einddatum]> #" & Formulieren!frmPerceelEigenaar!Einddatum0 & "#" OR IsNull([Einddatum])))

 

[edo janssen]

Hi Severin, Your sollution doesn't work. It gives a #name-error.

 

[Severin]

..try this..

=DSum("[TotaalHectare]";"qryPerceelEigenaar2";"[Eigenaar_id]='" & Formulieren!frmPerceelEigenaar!Klant_id & "' And [Wijzigingsdatum]< #" & Formulieren!frmPerceelEigenaar!Begindatum0 & "# And ([Einddatum]> #" & Formulieren!frmPerceelEigenaar!Einddatum0 & "# OR IsNull([Einddatum])"))

 

[edo janssen]

Already tried that, gave me #error, there's also a ) too much in the code.

 

[Severin]

=DSum("[TotaalHectare]";"qryPerceelEigenaar2";"[Eigenaar_id]='" & Formulieren!frmPerceelEigenaar!Klant_id & "' And [Wijzigingsdatum]< #" & Formulieren!frmPerceelEigenaar!Begindatum0 & "# And ([Einddatum]> #" & Formulieren!frmPerceelEigenaar!Einddatum0 & "# OR IsNull([Einddatum]))")

 

[edo janssen]

Yes, that's the trick. Thanks a lot!

 

[Severin]

You're welcome!~)