Counting records only under certain contitions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
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


Duane Hookom said:
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)



--
Duane Hookom
MS Access MVP
--

TomK said:
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
Click to expand...
Click to expand...
 
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.

--
Duane Hookom
MS Access MVP
--

TomK said:
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


Duane Hookom said:
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)



--
Duane Hookom
MS Access MVP
--

TomK said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Duane Hookom said:
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.

--
Duane Hookom
MS Access MVP
--

TomK said:
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


Duane Hookom said:
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)



--
Duane Hookom
MS Access MVP
--

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
Click to expand...
Click to expand...
Click to expand...
 
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.")



--
Duane Hookom
MS Access MVP
--

TomK said:
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

Duane Hookom said:
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.

--
Duane Hookom
MS Access MVP
--

TomK said:
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:
=Sum(ABS([Status/Reason] = "1 (ORDERED)"))
Try:
=Sum(ABS(([Status/Reason] <> "1 (ORDERED)"))
or
=Sum(IsNull([Status/Reason])+1)



--
Duane Hookom
MS Access MVP
--

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
Click to expand...
Click to expand...
Click to expand...
 
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

Duane Hookom said:
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.")



--
Duane Hookom
MS Access MVP
--

TomK said:
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

Duane Hookom said:
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.

--
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
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:
=Sum(ABS([Status/Reason] = "1 (ORDERED)"))
Try:
=Sum(ABS(([Status/Reason] <> "1 (ORDERED)"))
or
=Sum(IsNull([Status/Reason])+1)



--
Duane Hookom
MS Access MVP
--

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top