# Thread: Calculate Data between two dates

1. Registered User
Join Date
Sep 2007
Posts
2

## Unanswered: Calculate Data between two dates

A newbie and I have learned a lot by reading posts but cannot find this one. I have a database with several yes/no checkboxes ie, referredtoRn,Followup,phoneCall, etc. I can calculate how many yes boxes are in each field - I researched and found that answer. However I want a report of how many in each field for between this date and this date such as a monthly report. Thanks in advance.

2. Registered User
Join Date
Oct 2002
Location
Posts
697
Originally Posted by oscarpoo
A newbie and I have learned a lot by reading posts but cannot find this one. I have a database with several yes/no checkboxes ie, referredtoRn,Followup,phoneCall, etc. I can calculate how many yes boxes are in each field - I researched and found that answer. However I want a report of how many in each field for between this date and this date such as a monthly report. Thanks in advance.
Something like this would work as a subquery: (you could paste it into a field in the query design grid)

SELECT COUNT(*) FROM someTable WHERE referredtoRn = true AND someDateField BETWEEN fromDate AND toDate

If the BETWEEN operator doesn't work, try fromDate <= someDateFIeld AND someDateField <= toDate instead.

3. Registered User
Join Date
Sep 2007
Posts
2
Ok I have tried and can not figure it out. I am using a query Clinic Reports Query that refers to a table (Clinic Info) I have a DateModified field
In each place of the Yes/No such as
First grid - Count of ReferredToRN: Sum(IIf([ReferredToRN],1,0))
Second Grid - Count of FollowUp: Sum(IIf([FollowUp],1,0))
etc
with total as expression. it works great. but as soon as I put in under Datemodified it gives me one line for each yes instead of one line for each with a total I think I am supposed to place this

SELECT COUNT(*) FROM Clinic Info WHERE referredtoRn = true AND DateMotified BETWEEN Start Date AND End Date

in an empty grid but it did not work. It keeps saying the syntax of the subquery in the expression is incorrect. Check the subquery syntax and enclose the subquery in parenthesis.

Am I on the right track? Missing something or just totally confused at this point?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
you can count all your values in one pass
however Id suggest using sum, and only including true / false, or Boolean columns.

the SQL is somehting like...
select abs(sum(<mybooleancolumn01>)) as <myalias01>, abs(sum(<mybooleancolumn02>)) as <myalias02>,....
abs(sum(<mybooleancolumnxx>)) as <myaliasxx>
from <mytablename>
where <maydatecolumn> between <mysepecifiedstartdate> and mysepecifiedenddate);

..this takes advantage of the fact that Access stores true as -1, and false as zero, it should go without saying that this will only work for as long as JET represents true as -1 and false as zero
so summing the value of the column gives the (negative) number of occurances of that column, the abs converts its to positive

HTH
Last edited by healdem; 09-24-07 at 05:30.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•