Sum Cacluated Fields

[mboe]

I have an unbound calculated text box for each record on the report. I am trying to do a count on this calculated field but it always gives a pop up what that field value is.

I few variations I have tried are

=count(txtNew) , =count([txtNew]) and =count(iif([txtNew] = 1, 1, 0))

and I always get that popup. I have tried including me! and report name etc...

VERY IRRITATING!!! Any help would be appreciated!! Thanks.

 

[Rich]

Not sure what you're trying to do but you can repeat the calculation, or use the Running Sum property of an unbound control and hide it

 

[Pat Hartman]

You can't reference the control name in your calculation. You need to repeat the calculation.

=Sum(fldA + fldB) rather than =Sum(txtFldSum)

 

[ppoindexter]

i have the following code in the on format event of a report:


'changes date to white so it wont show unless it meets criteria
If Me.ReportDate.Value > Me!txtStart.Value And Me.ReportDate.Value < Me!txtEnd.Value Then
Me.ReportDate.ForeColor = 0
Else: Me.ReportDate.ForeColor = 16777215
End If

now, i need to count only those records that have the font (forecolor) black

i have tried several things but nothing is working

i cant use
=Count([ReportDate])
as it counts all records returned regardless of font color

anyone have any ideas??

 

[mboe]

Can you duplicate the logic from your onformat code.

Something like:

count(iif(Me.ReportDate.Value > Me!txtStart.Value And Me.ReportDate.Value < Me!txtEnd.Value ,0,1))

It will check the value and assign a 0 if it's in the parameters a 1 if out and then count all the ones.

I just typed this off the cuff so don't take the syntax as correct. Minus the "and" parameter I use counts like this all the time.

 

[Pat Hartman]

Rather than changing the color of the foreground, you should be changing the visible property. What if you print on colored paper?

If Me.ReportDate.Value > Me!txtStart.Value And Me.ReportDate.Value < Me!txtEnd.Value Then
    Me.ReportDate.Visible = True
Else 
    Me.ReportDate.Visible = False
End If

Count() always counts the numbe of records in the set. In your case Sum() will work better.

=Sum(IIf(ReportDate > txtStart And ReportDate < txtEnd, 1, 0))

Are you sure that your condition should be "> and <" rather than ">= and <" or "> and <="?

 

[ppoindexter]

Pat and mboe
Thank you so much for your replies. Pat, you were right, I was missing records that were on the first and last of a month. So I changed the code as you suggested. This code is working great now.

If Me.ReportDate.Value >= Me!txtStart.Value And Me.ReportDate.Value <= Me!txtEnd.Value Then
Me.ReportDate.Visible = True
Else
Me.ReportDate.Visible = False
End If

I placed the code below in a text box in the report footer.

=Sum(IIf([ReportDate]>=[txtStart] And [ReportDate]<=[txtEnd],1,0))

txtStart and txt End are text boxes (visible=no) in the details section. When I open the report I get "Enter Parameter Value" for "txtStart" "txtEnd" and "ReportDate"

any ideas?
Thanks again

 

[mboe]

ppoindexter

Do txtStart and txtEnd contain a formula or are they pulling from the underlying query? If they contain a formula you may have to use that in you sum formula instead of the "txtStart" reference. If their source is a field in an underlying table/query then I believe you should reference that source name and not the txtbox name.

Hope that helps.

Pat, thanks for pointing out the sum= instead of count= . I was thinking sum but typed count.

 

[ppoindexter]

mboe

txtStart and txtEnd are populated from a form, see below:

=Forms!frmReportByProvince!txtStartDate (populates txtStart)
=Forms!frmReportByProvince!txtEndDate (populates txtEnd)

i tried the following

=Sum(IIf([ReportDate]>=[Forms!frmReportByProvince!txtStartDate] And [ReportDate]<=[Forms!frmReportByProvince!txtEndDate],1,0))

the message i recieved was "invalid bracketing"

can you help with adding this to the code?
thanks again for your help