DCount by date range

[Hammond-Wandsworth]

I'm trying to calculate a count of a date field for those records that have
been added in the past 7 days. I want to display this in a report header and
I am adding the following code to the expression builder within the control
source. The code, however, is not working. I need to calculate several fields
this way and the code is not working on any of them. An example is below:

=DCount("[Alleged Breach Date]","[Breach Table]","[Alleged Breach
Date]=TRUE")>Date()-"7"

Can anyone see any mistakes with this code? Please let me know where I'm
going wrong. Thanks.

 

[Tom Lake]

I'm trying to calculate a count of a date field for those records that
have
been added in the past 7 days. I want to display this in a report header
and
I am adding the following code to the expression builder within the
control
source. The code, however, is not working. I need to calculate several
fields
this way and the code is not working on any of them. An example is below:

=DCount("[Alleged Breach Date]","[Breach Table]","[Alleged Breach
Date]=TRUE")>Date()-"7"

Can anyone see any mistakes with this code? Please let me know where I'm
going wrong. Thanks.

Try this:

=DCount("[Alleged Breach Date]", "[Breach Table]", "[Alleged Breach Date] >
" & Date() - 7)

Tom Lake

 

[John Spencer]

The following should work
= DCount("*","[Breach Table]","[Alleged Breach Date]> Date()-7")




--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'm trying to calculate a count of a date field for those records that
have
been added in the past 7 days. I want to display this in a report header
and
I am adding the following code to the expression builder within the
control
source. The code, however, is not working. I need to calculate several
fields
this way and the code is not working on any of them. An example is below:

=DCount("[Alleged Breach Date]","[Breach Table]","[Alleged Breach
Date]=TRUE")>Date()-"7"

Can anyone see any mistakes with this code? Please let me know where I'm
going wrong. Thanks.

Try this:

=DCount("[Alleged Breach Date]", "[Breach Table]", "[Alleged Breach Date]

" & Date() - 7)

Tom Lake

 

[Tom Lake]

The following should work
= DCount("*","[Breach Table]","[Alleged Breach Date]> Date()-7")

Will it know enough to expand the Date() if it's inside the quotes?

Tom Lake

 

[John Spencer]

Obviously I think so or I wouldn't have posted it that way. And my testing
indicates that it does. ;>)

By the way, your solution will probably fail since you must surround date
literals with "#"

=DCount("[Alleged Breach Date]", "[Breach Table]", "[Alleged Breach Date]

#" & Date() - 7 & "#")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

The following should work
= DCount("*","[Breach Table]","[Alleged Breach Date]> Date()-7")

Will it know enough to expand the Date() if it's inside the quotes?

Tom Lake

 

[Hammond-Wandsworth]

Thanks chaps, much appreciated. I used John's way and it seems to be working.
Thanks again.

Obviously I think so or I wouldn't have posted it that way. And my testing
indicates that it does. ;>)

By the way, your solution will probably fail since you must surround date
literals with "#"

=DCount("[Alleged Breach Date]", "[Breach Table]", "[Alleged Breach Date]

#" & Date() - 7 & "#")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

The following should work
= DCount("*","[Breach Table]","[Alleged Breach Date]> Date()-7")

Will it know enough to expand the Date() if it's inside the quotes?

Tom Lake