Summing up Data between two ranges

[Locopete99]

Hi Guys,

I'm building a database, and I'm nearly finished. I only have one thing left to do, but I'm working on 16 year old knowledge and missing a few bits.

I have a table that has the field [Date Received].

Is there a way to do a query that sums up the total records that fall between two ranges?

Whats more, I want the two ranges to be manually enterable.

In excel, my more natural territory, I could easily do this with sumifs where [Date Received]>field 1 and [Date Received] < Field 2.

Any help would be appreciated and feel free to put it as if I was a complete newbie!

 

[Ranman256]

Select sum(field) from table where [date] between #1/1/16# and #2/1/16#

 

[Locopete99]

Thanks Ranman,

It's not working though.

I've tried a few variations:

SELECT Sum [DATE REQUEST RECEIVED]
FROM Tbl_NPPR_LOG
WHERE  (date) Between #1/1/2016# And #1/2/2016#

This gives me a syntax error

SELECT Sum (Date)
FROM Tbl_NPPR_LOG
WHERE [DATE REQUEST RECEIVED]  Between #1/1/2016# And #1/2/2016#

This obviously asks me to define Date

And

SELECT Sum [DATE REQUEST RECEIVED]
FROM Tbl_NPPR_LOG
WHERE [DATE REQUEST RECEIVED]  Between #1/1/2016# And #1/2/2016#

Again, another syntax error.

 

[arnelgp]

use Count() in your query:

SELECT Count(*)
FROM Tbl_NPPR_LOG
WHERE [DATE REQUEST RECEIVED] Between #1/1/2016# And #1/2/2016#

 

[Locopete99]

OK, Thats worked.

As per my original post, is there a way that I can get the query to ask for the two dates, as the date range will always be moving and I won't be the person running the query?

Ideally, when the query is run, I would like two pop up boxes to appear and ask for date 1 and date 2 to check between.

 

[Ranman256]

if you use the query designer, you wont get syntax errors.

 

[Frothingslosh]

OK, Thats worked.

As per my original post, is there a way that I can get the query to ask for the two dates, as the date range will always be moving and I won't be the person running the query?

Ideally, when the query is run, I would like two pop up boxes to appear and ask for date 1 and date 2 to check between.

For pop-up boxes, the most straightforward way would be to just include the parameters [Start Date] and [End Date], like this:

SELECT Count(*)
FROM Tbl_NPPR_LOG
WHERE [DATE REQUEST RECEIVED] Between [Start Date] And [End Date]

Note that this approach will generate an error if the user's entry isn't a valid date. This kind of thing is usually done using forms because of that.

 

[Locopete99]

Brilliant!! Thanks guys, thats me done!