Count in Forms

[ErnieS]

I'm new to Access but have been able to accomplish a lot.
I need help with a "count" of records that contains certain information.
I have form Labeled "PPC data". The form included fields from a table labeled "PP Files".

I want to have a count of a field labeled "Lit Status" that has a value of "Remove From Inventory" as long as another field labeled "Lit Complete" has a value of "No".

I've tried to figure this out in expression builder but have had no luck.
Can anyone help me with this?

Thanks in advance.

[pbaldy]

Try

=Sum(IIf([Lit Status] = "Remove From Inventory" AND [Lit Complete] = "No", 1, 0))

The spaces in your field names will prove to be more trouble than they're worth in the long run.

[ErnieS]

No it didn't work. It didn't work like you posted so I tried this and it still didn't work:
=Sum(If([Litigation PP Files]![Lit Status]="Remove From Inventory" And [Litigation PP Files]![Lit Complete]="No",1,0))

The text box is "unbound". Does that matter. I didn't get any errors just nothing.

[pbaldy]

I should have mentioned that should be in the form footer. Is it? If Lit Complete is a Yes/No field in the table, that test would be:

[Lit Complete]=False

or

[Lit Complete]=0

[ErnieS]

Lit Complete is a yes/No field so I changed it as follows:
=Sum(If([Litigation PP Files]![Lit Status]="Remove From Inventory" And [Litigation PP Files]![Lit Complete]=0))

I also tried Lit Complete = False.
These did not work.

The text box that has this expression is not in the footer. It is in the Body of the Form which is where I wanted the count. Does this matter?

[pbaldy]

I think it will still work in the body, but you need to go back to the syntax I posted (adjusted for the Yes/No field). "If" won't work there.

[ErnieS]

Does that mean that I need to remove the "If" ?
Can you post the entire Expression?

[pbaldy]

=Sum(IIf([Lit Status] = "Remove From Inventory" AND [Lit Complete] = 0, 1, 0))

[ErnieS]

I really appreciate all of your help but it's not working.
I typed it like this:
=Sum(IIf([Litigation PP Files]![Lit Status]="Remove From Inventory" And [Litigation PP Files]![Lit Complete]=0,1,0))
Any other suggestions?

[pbaldy]

Can you post the db, or a sample of it?

[ErnieS]

So I changed this to:
=Count(IIf([Litigation PP Files]![Lit Status]="Remove From Inventory" And [Litigation PP Files]![Lit Complete]=False,1,0))

It won't work with "Sum". It works with "Count", however, it gives me all records and includes records that have other than "Remove From Inventory", and "Lit Complete NO.

[pbaldy]

I tested using Sum and a similar expression in a form and it worked as expected. I take it you can't post the db, or a sample to test with?

[ErnieS]

Unfortunately I can't post it as it has confidential information.
If I change to "sum" the I get 0
Using "Count" it counts all of my records and does not exclude the fields that I want excluded.

[pbaldy]

Make sure the first field doesn't have a table level lookup defined. It could be storing an ID number but displaying the text. If that's the case, you would need to change your test to the appropriate number.