Date Filtering within a Formula

[JeanZander]

I am new to this forum, so I apologize in advance for not searching for the answer to my question first. I have a formula that works, but does not complete the desired result. It is adding the total of monies recouped from selling surplus properties. The problem is I need to know the total dollars recouped during a certain period 7/1/12 thru 6/30/13. The field needed to access this information is called [Disp Date]. Any takers?!



=DSum("[$$ Disposition Proceeds]","[UTA Property Database]")

[orange]

First recommendation -- DO NOT use embedded spaces or special characters in Access field or object names.
second recommendation -- review the examples here
http://www.techonthenet.com/access/f...omain/dsum.php

[JeanZander]

Thanks, but my expressions seem to work fine with the embedded spaces and special characters; unfortunately, the examples you suggested don't seem to include expressions filtering dates. I guess I can filter the dates manually from my form. I just thought someone might know the syntax to filter a date range within an expression. Thanks anyway.

[rpeare]

=DSum("[$$ Disposition Proceeds]","[UTA Property Database]","[FIELDNAME] = 'VALUE'")

for a text field

=DSum("[$$ Disposition Proceeds]","[UTA Property Database]", "[FIELDNAME] = VALUE")

for a number field

=DSum("[$$ Disposition Proceeds]","[UTA Property Database]", "[FIELDNAME] = #VALUE#")

for a date field

[orange]

Try this

=DSum("[$$ Disposition Proceeds]","[UTA Property Database]","[Disp Date] between #7/1/12# AND #6/30/13#")

[JeanZander]

Thanks Orange!!! You're a genius...to me! That worked perfectly. Just one question, can I add: , "0" before the last quote or something so if the answer is zero it puts in a zero rather than a blank box?

[orange]

You can try this, but I haven't tested it


=IIF(DSum("[$$ Disposition Proceeds]","[UTA Property Database]","[Disp Date] between #7/1/12# AND #6/30/13#")>0,DSum("[$$ Disposition Proceeds]","[UTA Property Database]","[Disp Date] between #7/1/12# AND #6/30/13#"),0)

This is an immediate IF (IIF) -below is the syntax.
IIf (condition, truepart, falsepart)

[JeanZander]

BRAVO!! It was brilliant. Worked perfectly

[orange]

Glad to help.

Here's a great reference if you're interested
http://www.accessmvp.com/strive4peace/