Unanswered: Using Build Expression to Count by Month
I am creating a report in access to track participation in a research study. The report needs to count the number of persons that have agreed to participate by month and keep a tally of the total number of persons throughout the year.
So the report needs to look like this:
Month Agreed to Participate
and so on...
Yearly Total 8
I've managed to get it to count using the build expression:
=Count(IIf([WillParticipate]="Agreed",0)) and that part works.
But I dont know how to use the build expression to make it:
1. Know how to read the dates in my date field [AgreeanceDate] as Jan-07 since they are entered as 01/05/07.
2. Separate it by month.
I haven't used VB for about 8 years so want to try to avoid getting into using actual language if possible.
Just in case anyone else needs the know how...I did some searches on the board but none were quite what I needed without using the language.
Using query wizard: Selected my checkbox field for [AgreedtoParticipate] and the date field [AgreedtoParticpateDate].
Next, I chose the summary option and checked the sum box and the count box on the bottom right.
Next, I choose to sort by Month.
Then, when I looked at my query table, it was also giving me the sum of those unchecked, which was not needed.
So I went into the design of the form, and in the criteria field under the [Sum of AgreedtoParticipate] column, i typed in True and that eliminated the unchecked sum.
And then I was easily able to use the wizard to create a report based off that query that broke it out by month. Then to get a running talley - I used the toolbox to insert a text field. Right clicked and went to build expression - =Sum([Count Of TABLENAME]).
I'm actually having more issues...and so am wondering if your easy solution might work for this.
I need to produce a summary report of participants in a research study.
Here are the steps:
1. We first send out an initial/introduction letter telling the person about the study and that we will be calling them in two weeks to see if they will participate.
2. We then call them and they either agree, refuse, or they are unable to be contacted (phone disconnected maybe).
3. If they agree to participate, we mail a packet with the materials they need to fill out.
4. Then will also need to note when the packet of materials is returned completed.
5. All of these things may be happening in different months.
Fields that are included:
[InitialLetterMailed] - a checkbox - check when letter is mailed (ILM)
[InitialLetterMailedDate] - date field - update when letter is mailed
[ParticipationContactStatus] - a combo box - choices: Agreed (AtP), Refused, (RtP) Unable to be Contacted (UtC)
[ParticipationContactStatusDate]- date field - update whenever change Participation Status
[PacketMailed] - a checkbox - check when packet of materials is mailed (PM)
[PacketMailedDate] - date field - update when packet of materials is mailed
[PacketReceived] - a checkbox - check when packet of materials is returned (PR)
[PacketRecdDate] - date field - update when packet of materials is returned
My report summary needs to look like this:
A B C D E F
ILM AtP RtP UtC PM PR
Jan-07 2 1 0 0 0 0
Feb-07 0 1 0 0 2 1
Mar-07 0 0 0 0 0 1
Total 2 2 0 0 2 2
Examples to illustrate:
1. Sally's initial letter is mailed (ILM) in Jan (A1 plus 1). We contact her in Jan and she agrees to participate (ATP) (B1 plus 1). Her packet is mailed in Feb (E2 plus 1). She returns her packet in March (F3 plus 1).
2. Bob' initial letter is mailed in Jan (A1 plus 1). We reach him in Feb and he agrees to participate (B2 plus 1). His packet is mailed in Feb (E2 plus 1). His packet is received in Feb (F2 plus 1).