Report Footer Sum

[Guest]

I am trying to get the sum of a field in my report. This field is called
Contract_Total. The problem I am having is that in this Field it contains the
Words: WARRANTY, N/C, and ??. I want to sum up the Contract total
disregarding WARRANTY, N/C, and ??. Besides those 3 items the rest of the
field is populated with dollar amounts. I have tried this:

=sum(iif([Contract_Total] <> "WARRANTY" and "N/C" and "??", 0))

This gives me a sum of $0.00 which isnt correct since the dollar amounts in
the report are of values $500 +. I have tried everything I can think of. Any
Help would be greatly appreciated.

c. ascheman

 

[Duane Hookom]

What field contains the amount you want to total and what field contains the
values like "WARRANTY"?

Generically, if you want to total a Sales field where Type is not "Lost",
"Returns", or "Broken", you could use

=Sum( Abs([Type] Not In ("Lost","Returns","Broken")) * [Sales])

 

[Guest]

Tried what you suggested, and I still am not getting the sum of the Contract
Total field. In the Contract Total field there are dollar amounts as well as
the words "WARRANTY", "N/C", and "??". I want to sum the Contract total, but
because of the words in the field it throughs an error. I am trying to find a
way to total the contract totals field, but ignoring anything that says
WARRANTY, N/C, or ??. I still want the words "WARRANTY", "N/C", and "??" to
be displayed in the report but for the sum box in the report footer to ignore
these 3 words when it does the sum for the field Contract_Total.
Thanks for the assistace.

c. ascheman

What field contains the amount you want to total and what field contains the
values like "WARRANTY"?

Generically, if you want to total a Sales field where Type is not "Lost",
"Returns", or "Broken", you could use

=Sum( Abs([Type] Not In ("Lost","Returns","Broken")) * [Sales])

--
Duane Hookom
MS Access MVP
--

I am trying to get the sum of a field in my report. This field is called
Contract_Total. The problem I am having is that in this Field it contains
the
Words: WARRANTY, N/C, and ??. I want to sum up the Contract total
disregarding WARRANTY, N/C, and ??. Besides those 3 items the rest of the
field is populated with dollar amounts. I have tried this:

=sum(iif([Contract_Total] <> "WARRANTY" and "N/C" and "??", 0))

This gives me a sum of $0.00 which isnt correct since the dollar amounts
in
the report are of values $500 +. I have tried everything I can think of.
Any
Help would be greatly appreciated.

c. ascheman

 

[Duane Hookom]

You may need to create a user-defined function that accepts your mixture of
text and numbers and returns a numeric value if the text is of a specific
value. I expect that quite a number of readers here could provide the code
for you if you would provide use with about 10 sample miscellaneous values
from this field and the expected sum value.

In the future, you should not put multiple values in a single field.

--
Duane Hookom
MS Access MVP

Tried what you suggested, and I still am not getting the sum of the
Contract
Total field. In the Contract Total field there are dollar amounts as well
as
the words "WARRANTY", "N/C", and "??". I want to sum the Contract total,
but
because of the words in the field it throughs an error. I am trying to
find a
way to total the contract totals field, but ignoring anything that says
WARRANTY, N/C, or ??. I still want the words "WARRANTY", "N/C", and "??"
to
be displayed in the report but for the sum box in the report footer to
ignore
these 3 words when it does the sum for the field Contract_Total.
Thanks for the assistace.

c. ascheman

What field contains the amount you want to total and what field contains
the
values like "WARRANTY"?

Generically, if you want to total a Sales field where Type is not "Lost",
"Returns", or "Broken", you could use

=Sum( Abs([Type] Not In ("Lost","Returns","Broken")) * [Sales])

--
Duane Hookom
MS Access MVP
--

I am trying to get the sum of a field in my report. This field is called
Contract_Total. The problem I am having is that in this Field it
contains
the
Words: WARRANTY, N/C, and ??. I want to sum up the Contract total
disregarding WARRANTY, N/C, and ??. Besides those 3 items the rest of
the
field is populated with dollar amounts. I have tried this:

=sum(iif([Contract_Total] <> "WARRANTY" and "N/C" and "??", 0))

This gives me a sum of $0.00 which isnt correct since the dollar
amounts
in
the report are of values $500 +. I have tried everything I can think
of.
Any
Help would be greatly appreciated.

c. ascheman

 

[Guest]

Here are some sample values of what is contained in the Contract_Total field.

$3,000.00
N/C
$650.00
$756.00
WARRANTY
$15,000.00
??
$4,500.00

The Contract_Total = $330,400.00 (got the value by eliminating the words
"WARRANTY", "N/C", and "??" from another report via a Query).

Im needing the total value of the numbers, and for the words N/C, WARRANTY,
and ?? to be ignored while being summed. This is an Works to Access
conversion. The old Works database was setup years before I started working
for my employer. If it was my choice I would eliminate the words "WARRANTY",
"N/C", and "??" all together, and make my life much easier. Unfortunately my
boss wants those left in as that is the way he is used to the program. So now
its upto me to figure out a way to get the sum for the Contract_Total field,
and to keep those words in the report. I thank you for your assistance so
far, and any further assistance will be greatly appreciated.

c. ascheman

You may need to create a user-defined function that accepts your mixture of
text and numbers and returns a numeric value if the text is of a specific
value. I expect that quite a number of readers here could provide the code
for you if you would provide use with about 10 sample miscellaneous values
from this field and the expected sum value.

In the future, you should not put multiple values in a single field.

--
Duane Hookom
MS Access MVP

Tried what you suggested, and I still am not getting the sum of the
Contract
Total field. In the Contract Total field there are dollar amounts as well
as
the words "WARRANTY", "N/C", and "??". I want to sum the Contract total,
but
because of the words in the field it throughs an error. I am trying to
find a
way to total the contract totals field, but ignoring anything that says
WARRANTY, N/C, or ??. I still want the words "WARRANTY", "N/C", and "??"
to
be displayed in the report but for the sum box in the report footer to
ignore
these 3 words when it does the sum for the field Contract_Total.
Thanks for the assistace.

c. ascheman

What field contains the amount you want to total and what field contains
the
values like "WARRANTY"?

Generically, if you want to total a Sales field where Type is not "Lost",
"Returns", or "Broken", you could use

=Sum( Abs([Type] Not In ("Lost","Returns","Broken")) * [Sales])

--
Duane Hookom
MS Access MVP
--

I am trying to get the sum of a field in my report. This field is called
Contract_Total. The problem I am having is that in this Field it
contains
the
Words: WARRANTY, N/C, and ??. I want to sum up the Contract total
disregarding WARRANTY, N/C, and ??. Besides those 3 items the rest of
the
field is populated with dollar amounts. I have tried this:

=sum(iif([Contract_Total] <> "WARRANTY" and "N/C" and "??", 0))

This gives me a sum of $0.00 which isnt correct since the dollar
amounts
in
the report are of values $500 +. I have tried everything I can think
of.
Any
Help would be greatly appreciated.

c. ascheman

 

[Guest]

Thanks for all your help Duane. I found what I needed and got it to work
perfectly. Here is the code I used:

=Sum(IIf(IsNumeric([Contract_Total]),([Contract_Total]),0))

This gave me the answer I needed. Thanks again.

c. ascheman

Here are some sample values of what is contained in the Contract_Total field.

$3,000.00
N/C
$650.00
$756.00
WARRANTY
$15,000.00
??
$4,500.00

The Contract_Total = $330,400.00 (got the value by eliminating the words
"WARRANTY", "N/C", and "??" from another report via a Query).

Im needing the total value of the numbers, and for the words N/C, WARRANTY,
and ?? to be ignored while being summed. This is an Works to Access
conversion. The old Works database was setup years before I started working
for my employer. If it was my choice I would eliminate the words "WARRANTY",
"N/C", and "??" all together, and make my life much easier. Unfortunately my
boss wants those left in as that is the way he is used to the program. So now
its upto me to figure out a way to get the sum for the Contract_Total field,
and to keep those words in the report. I thank you for your assistance so
far, and any further assistance will be greatly appreciated.

c. ascheman

You may need to create a user-defined function that accepts your mixture of
text and numbers and returns a numeric value if the text is of a specific
value. I expect that quite a number of readers here could provide the code
for you if you would provide use with about 10 sample miscellaneous values
from this field and the expected sum value.

In the future, you should not put multiple values in a single field.

--
Duane Hookom
MS Access MVP

Tried what you suggested, and I still am not getting the sum of the
Contract
Total field. In the Contract Total field there are dollar amounts as well
as
the words "WARRANTY", "N/C", and "??". I want to sum the Contract total,
but
because of the words in the field it throughs an error. I am trying to
find a
way to total the contract totals field, but ignoring anything that says
WARRANTY, N/C, or ??. I still want the words "WARRANTY", "N/C", and "??"
to
be displayed in the report but for the sum box in the report footer to
ignore
these 3 words when it does the sum for the field Contract_Total.
Thanks for the assistace.

c. ascheman

:

What field contains the amount you want to total and what field contains
the
values like "WARRANTY"?

Generically, if you want to total a Sales field where Type is not "Lost",
"Returns", or "Broken", you could use

=Sum( Abs([Type] Not In ("Lost","Returns","Broken")) * [Sales])

--
Duane Hookom
MS Access MVP
--

I am trying to get the sum of a field in my report. This field is called
Contract_Total. The problem I am having is that in this Field it
contains
the
Words: WARRANTY, N/C, and ??. I want to sum up the Contract total
disregarding WARRANTY, N/C, and ??. Besides those 3 items the rest of
the
field is populated with dollar amounts. I have tried this:

=sum(iif([Contract_Total] <> "WARRANTY" and "N/C" and "??", 0))

This gives me a sum of $0.00 which isnt correct since the dollar
amounts
in
the report are of values $500 +. I have tried everything I can think
of.
Any
Help would be greatly appreciated.

c. ascheman

 

[Duane Hookom]

I would create a function and use it in your report or group header or
footer like:

=Sum(GetContractValue([Contract_Total]))

Function GetContractValue(pvarCT As Variant) As Double
If Len(pvarCT & "") > 0 Then
pvarCT = Replace(Replace(pvarCT, ",", ""), "$", "")
End If
If IsNumeric(pvarCT) Then
GetContractValue = Val(pvarCT)
Else
GetContractValue = 0
End If
End Function

--
Duane Hookom
MS Access MVP
--

Here are some sample values of what is contained in the Contract_Total
field.

$3,000.00
N/C
$650.00
$756.00
WARRANTY
$15,000.00
??
$4,500.00

The Contract_Total = $330,400.00 (got the value by eliminating the words
"WARRANTY", "N/C", and "??" from another report via a Query).

Im needing the total value of the numbers, and for the words N/C,
WARRANTY,
and ?? to be ignored while being summed. This is an Works to Access
conversion. The old Works database was setup years before I started
working
for my employer. If it was my choice I would eliminate the words
"WARRANTY",
"N/C", and "??" all together, and make my life much easier. Unfortunately
my
boss wants those left in as that is the way he is used to the program. So
now
its upto me to figure out a way to get the sum for the Contract_Total
field,
and to keep those words in the report. I thank you for your assistance so
far, and any further assistance will be greatly appreciated.

c. ascheman

You may need to create a user-defined function that accepts your mixture
of
text and numbers and returns a numeric value if the text is of a specific
value. I expect that quite a number of readers here could provide the
code
for you if you would provide use with about 10 sample miscellaneous
values
from this field and the expected sum value.

In the future, you should not put multiple values in a single field.

--
Duane Hookom
MS Access MVP

Tried what you suggested, and I still am not getting the sum of the
Contract
Total field. In the Contract Total field there are dollar amounts as
well
as
the words "WARRANTY", "N/C", and "??". I want to sum the Contract
total,
but
because of the words in the field it throughs an error. I am trying to
find a
way to total the contract totals field, but ignoring anything that says
WARRANTY, N/C, or ??. I still want the words "WARRANTY", "N/C", and
"??"
to
be displayed in the report but for the sum box in the report footer to
ignore
these 3 words when it does the sum for the field Contract_Total.
Thanks for the assistace.

c. ascheman

:

What field contains the amount you want to total and what field
contains
the
values like "WARRANTY"?

Generically, if you want to total a Sales field where Type is not
"Lost",
"Returns", or "Broken", you could use

=Sum( Abs([Type] Not In ("Lost","Returns","Broken")) * [Sales])

--
Duane Hookom
MS Access MVP
--

I am trying to get the sum of a field in my report. This field is
called
Contract_Total. The problem I am having is that in this Field it
contains
the
Words: WARRANTY, N/C, and ??. I want to sum up the Contract total
disregarding WARRANTY, N/C, and ??. Besides those 3 items the rest
of
the
field is populated with dollar amounts. I have tried this:

=sum(iif([Contract_Total] <> "WARRANTY" and "N/C" and "??", 0))

This gives me a sum of $0.00 which isnt correct since the dollar
amounts
in
the report are of values $500 +. I have tried everything I can think
of.
Any
Help would be greatly appreciated.

c. ascheman