Counting Problem With Text Boxes

D

Drummer2004

I have a report and I need to be able to add up the amount of records
that are equal to 100% and the invoiced amount is greater than 0 as
well as the ones that are at 100 but ARE equal to 0. I am having
difficulties with the code to get this working however. Here's what I
have so far:

The first 2 are a check box field in the table
=Count([Invoice Status])=-1 (supposed to count the number of times that
invoice status = -1)

=Count([Invoice Status])=0 (supposed to count the number of times that
invoice status = -0)

=Sum([Amount Invoiced (CDN)]) (Supposed to calculate the dollar value
of the items that are invoiced)

=Sum([Estimated Billing (CDN)]) (Supposed to calculate the dollar value
of the items that are not invoiced)

=Count([Master Job No]) (Counts total records)

=Sum([Amount Invoiced (CDN)])+Sum([Estimated Billing (CDN)])
(Supposed to calculate total dollar value of all records)

Any help would be appreciated
 
J

Jeff Boyce

"I am having difficulties..." ... like what?

"Here's what I have ..." Where? In which section? Or in which event?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
 
J

J

That code is in the report footer section of my report. The
difficulties that I'm having is that the code that I'm using doesn't
seem to work and I need to know how to get it to do what I want as
stated above.
 
J

John Spencer

The first 2 are incorrect. You probably want.
=Abs(Sum([Invoice Status]=-1))

=Abs(Sum([Invoice Status]=0 ))

These should all work assuming that you are referring to fields in the
record source of the report and your control names are not the same as the
field names.

=Sum([Amount Invoiced (CDN)]) (Supposed to calculate the dollar value
of the items that are invoiced)

=Sum([Estimated Billing (CDN)]) (Supposed to calculate the dollar value
of the items that are not invoiced)

=Count([Master Job No])

=Sum([Amount Invoiced (CDN)])+Sum([Estimated Billing (CDN)])

Are these controls in the REPORT footer (not the PAGE footer)?
 
J

Jeff Boyce

"Doesn't seem to work" could mean that nothing shows... or the wrong number
shows, ... or you get an error message, ... ?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
 
J

J

There's no error message, it's just that nothing shows up. Just a
blank. Also, I have checked to see that visibility of the text box is
set to yes so it wouldn't be that.
 
J

John Spencer

If the calculations are in the Report Footer then they should show up. If
they are in the Page footer then you should get an error.

Try this test. Just set the control source of each control equal to some
number.
Does that display when you run the report?

You never actually said what the problem was. You said
"I am having difficulties with the code to get this working however. Here's
what I have so far:"

However, we don't know if you are getting an error message, no results at
all, or wrong results. If the results are wrong, we don't know how they are
wrong (too large, too small, zero, etc)
 
B

BruceM

Forgetting the footer for the moment, if you set the Running Sum property
for the AmountInvoiced text box (or the EstimatedBilling text box) to Yes,
what happens? Have you considered grouping on InvoiceStatus, and summing in
the group footer?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top