Counting Records

[J]

I have a text box and I need to use it to count the number of records
that contain both a completion status of 100 and the Invoice status
being equal to True (it's a check box). What I have so far is:

=(DCount("[Job]", "Status wk 01", "([Completion Status] = "100" AND
[Invoice Status] = "True")

This is supposed to count the Job field for any record in the "Status
wk 01" table that has 100 completion status and a checked box for
Invoice Status. I get an error saying that there is something wrong
with the syntax and that apparently I've entered an operand without an
operator. If anyone could tell me what I'm doing wrong or an alternate
method of fixing my problem then it would be appreciated.

 

[Duane Hookom]

We don't know if Completion Status is numeric or text. If it is numeric,
try:
=(DCount("[Job]", "Status wk 01", "([Completion Status] = 100 AND [Invoice
Status] = -1")

Also, if you only want to count the records from your report with this
criteria, use:
=Sum(Abs([Completion Status] = 100 AND [Invoice Status] = -1))

This last expression would be much more efficient if it meets your needs.

 

[John Spencer]

Assumption
Completion Status is a number and Invoice Status is a Yes/No (boolean)
field.

=DCount("[Job]", "Status wk 01", "[Completion Status] = 100 AND [Invoice
Status] = True")

You might also be able to use this formula in your report textbox.

=Abs(Sum([Completion Status] = 100 AND [Invoice Status] = True))

That formula sums the number of time the expression is True. ( In Access
False is zero, True is -1.) The Abs then strips off the negative sign to
give you a positive count.

 

[J]

Completion Status is a number field. There is however an error with
the "Sum" solution. There is apparently an invalid string that I need
to figure out.

 

[J]

This report also does a filter at the start for a job number. Is it
possible to use:

=DCount("[Job]", "Status wk 01", "[Completion Status] = 100 AND
[Invoice Status] = True")

and incorporate something into it that will allow me to filter the
table for whatever job number that the user enters into the box when
the report is run?

 

[Duane Hookom]

That's why both John and I suggested using:

=Sum(Abs([Completion Status] = 100 AND [Invoice Status] = -1))

This expression would work in a Group or Report Header or Footer (not page
sections).

 

[J]

I have it in the report footer but it's bringing back an error about an
incorrect string. It says that a string can only be 255 characters in
length including opening and closing parenthesis. How that applies to
the formula is beyond me because it's definately less than 255
characters.

 

[Duane Hookom]

Delete the text box and try run the report. Add the text box back in to the
report footer and add the control source. Test.

 

[J]

I created a group footer and added in the formula. I made the text box
invisible and made a box in the report footer equal to the group footer
text box.

I'm using:

=Count([Completion Status]=100 And [Invoice Status]=0)

But the result always comes back as 1. Any suggestions?

 

[J]

oh, and if I use:

=Abs(Sum([Completion Status] = 100 AND [Invoice Status] = True))

I get a result of 0

 

[Duane Hookom]

I am not sure why you are using invisible text boxes etc. What do you get if
you add text boxes to your report footer section with control sources of:
=Abs(Sum([Completion Status] = 100 AND [Invoice Status] = True))
=Abs(Sum([Invoice Status] = True))
=Abs(Sum([Completion Status] = 100)
=Abs(Sum([Completion Status] = 100 AND [Invoice Status] = False))
=Abs(Sum([Completion Status] <> 100 AND [Invoice Status] = False))

This is all basic trouble-shooting steps that might identify your problems.

 

[J]

With what you have there, I get:

0
0
3
3
4

The last 2 help me out...I don't know how I could have overlooked that
before. Thanks a lot