Counting records only under certain contitions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TomK - 11 Jan 2006 21:16 GMT

I have a data base of qoutes issued during the year. I am trying to generate
a report giving total number of quotes as well as the total number of quotes
that generated an order. Within the data base is a field "Status/Reason"
that contains either the string "1 (ORDERED)" or is null. I have tried the
following statement in the status/reason grouping footer:
=IIF ([Status/Reason]="1 (ORDERED)", Count ([Status/Reason])). I get a
null response. (There should be 6). Have also tried =sum (ABS
([Status/Reason] = "1 (ORDERED)")) With the same result.

Any help would be appreciated.

Thanks,
Tom

Reply to this Message

Duane Hookom - 11 Jan 2006 22:53 GMT

Trouble-shoot.
I would expect this to work:
=Sum(ABS([Status/Reason] = "1 (ORDERED)"))
Try:
=Sum(ABS(([Status/Reason] <> "1 (ORDERED)"))
or
=Sum(IsNull([Status/Reason])+1)

Signature

Duane Hookom
MS Access MVP
--

>I have a data base of qoutes issued during the year. I am trying to
>generate
[quoted text clipped - 12 lines]
> Thanks,
> Tom

Reply to this Message

TomK - 12 Jan 2006 15:03 GMT

I am entering this in a text box in the Status/Reason Footer. For some
reason the "Build" button on the tool bar is grayed out so I manually type in
the expression.

When I tried them I got the following error:

The Expression On Format you entered as the event property setting produced
the following error: The function you entered can't be used in this
expression.
* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating, event, or macro.

Thanks for the quick reply. I am using Office XP and Windows XP is the OS.

Tom

> Trouble-shoot.
> I would expect this to work:
[quoted text clipped - 20 lines]
> > Thanks,
> > Tom

Reply to this Message

Duane Hookom - 12 Jan 2006 15:12 GMT

I would check the On Format property of all of your report sections in the
report. It doesn't sound like your expression in the text box control source
has anything to do with your problem.

Signature

Duane Hookom
MS Access MVP
--

>I am entering this in a text box in the Status/Reason Footer. For some
> reason the "Build" button on the tool bar is grayed out so I manually type
[quoted text clipped - 41 lines]
>> > Thanks,
>> > Tom

Reply to this Message

TomK - 12 Jan 2006 22:01 GMT

Thanks. I have tried in vain to determine what the On Format property for
the sections is supposed to be. The "Help" did not help me. It was about
formating input like dates etc.

In one section of the report the On Format property is blank in another it
had the expression from the text box. I guess what I am asking is what does
the On Format property do?

I hope you can clarify this for me. At least I no longer get the error.
But I do not get the correct results either. What is happening is that it
groups the qoutes that have become orders but does not count them.

Appreciate your time and effort in this. Can get other reports to count
total quotes with =CountRecord in a text box.

Tom

> I would check the On Format property of all of your report sections in the
> report. It doesn't sound like your expression in the text box control source
[quoted text clipped - 45 lines]
> >> > Thanks,
> >> > Tom

Reply to this Message

Duane Hookom - 13 Jan 2006 02:04 GMT

The "On ..." properties are event properties. You use these to assign code
or macros or functions to events. For instance, you can add the MsgBox()
function to the On Open event property of your report.
On Open: =MsgBox("Report is opening.")

Signature

Duane Hookom
MS Access MVP
--

> Thanks. I have tried in vain to determine what the On Format property for
> the sections is supposed to be. The "Help" did not help me. It was about
[quoted text clipped - 69 lines]
>> >> > Thanks,
>> >> > Tom

Reply to this Message

TomK - 17 Jan 2006 16:17 GMT

Duane:
Sorry about the delay in responding but I had to move on to another project.
When I got back to this report I was able to get the count by putting the
expression in the report footer but not the "Status/Reason" group footer.
Not sure why but it worked. Thanks, TomK

> The "On ..." properties are event properties. You use these to assign code
> or macros or functions to events. For instance, you can add the MsgBox()
[quoted text clipped - 74 lines]
> >> >> > Thanks,
> >> >> > Tom

Reply to this Message