Query-count field name

[10317]

I wanted to show a total of defect types at the bottom of a "date sensitive" report as follow:

Total ER defect: 30
Total IR defect: 23

The field name in the main database is "type", the data for "type" is ER or IR.

I put in ER1:count([type]="ER")in the query (ER1 is not a field in the main database)

Why won't ACCESS count the number of ER in the query and dump it in ER1 so that I can use ER1 in my report?

 

[PHV]

ER1: Sum(IIf([type]='ER1',1,0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[10317]

I got a message:

You tried to execute a query that does not include the specified expression <name> as part of an aggregate function (error 3122)

 

[dhookom]

You don't need to do anything in your query. Add text boxes to your report footer with control sources like:
=Sum(Abs([Type]="ER"))
and
=Sum(Abs([Type]="IR"))



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[10317]

Thanks a lot! It works. What is Abs? If Type is a numeric field, do I need Abs or/and " "?

 

[PHV]

([Type]="IR") is a logical expression returning a boolean value: -1 if true, 0 if false.
So, you sum the absolute values of this expression.

Another way, as suggested in my previous post:
=Sum(IIf([type]='ER',1,0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886