Formula for unbound text box not working...

[sjl]

Formula for text box not working in Report

Hi All,

I am trying to place this formula in the Control Source of a text box placed in a Report. It is not working. Any ideas what am I doing wrong?
I want it to count the occurrences when Review Type=Full and when month is July (Jul) for a field of dates

=IIf([strRevTypeNIRB]="Full" And Format([dtmNPacketSubmitDate],"mmm") Like "Jul",Count([dtmNPacketSubmitDate]),Null)

It is either not counting the number of occurrences at all or it counts all the occurrences (regardless of month)

thanks for any suggestions....
sjl

 

[sjl]

Pat,
superb. thanks a BUNCH. appreciate the explanation.

sjl

 

[sjl]

I did get this formula to work (which is a variation of that was posted).

=Sum(IIf([strRevTypeNIRB]="Full" And Format([dtmNPacketSubmitDate],"mmm")="Jan",1,0))

However, now I am realizing that it only records a "1" or a "0" (which is now obvious to me

HOWEVER, what I want to happen is to record the exact number of dates that were posted for each month, for Full Reviews:

e.g.

for Full Review, the dates Packets were Submitted are (all come from a subForm on my Form):

Jan's dtmNPacketSubmitDates:
01/14/2007,
01/22/2007
so n=2

Feb: no Packets were submitted in Feb,
so n= 0


Mar's dtmNPacketSubmitDates:
03/03/2007
03/09/2007
03/11/2007
03/30/2007
so n=4

etc....

So I want my Report to look like this for the above 3 months of 2007:

MONTH Full Reviews:
Jan:______2
Feb:______0
Mar:______4
etc....


Can I not get a COUNT of the number of dates posted, by month, for my Report?

 

[JoeyY]

Do you want a list by month and a count of full reviews?

If so, I would be using a 'Total' query

Column----------------------------------Total Row--Criteria
Month: month([dtmNPacketSubmitDate])--'Group By'
[strRevTypeNIRB]------------------------'Where'----"Full"
Full Reviews: [dtmNPacketSubmitDate]----'Count'