Calculated Totals

[Guest]

Hello,

I have a table that processes both credits and invoices, as needed, using
the same form. Now I need to create a monthly report that shows the total
credits and total invoices by product.

I tried to use an IF statement that would total all records with credits (if
the credit checkbox was selected) and would total the invoices for all
records with the invoice checkbox selected. This did not give me an error
but does not give me a value. Please see the respective calculations below:

=IIf([CreditRequest]="Yes",Sum([Quantity]*[Unit Price]),"")
=IIf([InvoiceRequest]="Yes",Sum([Quantity]*[Unit Price]),"")

The following formula will give me a total, by product, of both together but
they need to be displayed separately.

=Sum([Quantity]*[Unit Price])

What am I doing wrong? Can someone please help me to make this work?

Thanks,
Jim

 

[tina]

assuming that the "checkbox" you refer to is actually a field with a Yes/No
data type, in the underlying table, try

=Sum(IIf([CreditRequest]=True,[Quantity]*[Unit Price],0))

hth

 

[Guest]

Hi Tina,

Your assumption was correct and your suggested calculation worked perfectly.
Thank you for your assistance.

Sincerely,
Jim

assuming that the "checkbox" you refer to is actually a field with a Yes/No
data type, in the underlying table, try

=Sum(IIf([CreditRequest]=True,[Quantity]*[Unit Price],0))

hth

Hello,

I have a table that processes both credits and invoices, as needed, using
the same form. Now I need to create a monthly report that shows the total
credits and total invoices by product.

I tried to use an IF statement that would total all records with credits (if
the credit checkbox was selected) and would total the invoices for all
records with the invoice checkbox selected. This did not give me an error
but does not give me a value. Please see the respective calculations below:

=IIf([CreditRequest]="Yes",Sum([Quantity]*[Unit Price]),"")
=IIf([InvoiceRequest]="Yes",Sum([Quantity]*[Unit Price]),"")

The following formula will give me a total, by product, of both together but
they need to be displayed separately.

=Sum([Quantity]*[Unit Price])

What am I doing wrong? Can someone please help me to make this work?

Thanks,
Jim

 

[tina]

you're welcome

Hi Tina,

Your assumption was correct and your suggested calculation worked perfectly.
Thank you for your assistance.

Sincerely,
Jim

assuming that the "checkbox" you refer to is actually a field with a Yes/No
data type, in the underlying table, try

=Sum(IIf([CreditRequest]=True,[Quantity]*[Unit Price],0))

hth

Hello,

I have a table that processes both credits and invoices, as needed, using
the same form. Now I need to create a monthly report that shows the total
credits and total invoices by product.

I tried to use an IF statement that would total all records with

credits
(if

the credit checkbox was selected) and would total the invoices for all
records with the invoice checkbox selected. This did not give me an error
but does not give me a value. Please see the respective calculations below:

=IIf([CreditRequest]="Yes",Sum([Quantity]*[Unit Price]),"")
=IIf([InvoiceRequest]="Yes",Sum([Quantity]*[Unit Price]),"")

The following formula will give me a total, by product, of both

together
but

they need to be displayed separately.

=Sum([Quantity]*[Unit Price])

What am I doing wrong? Can someone please help me to make this work?

Thanks,
Jim

 

[Guest]

Hi Tina,

Thanks again for you help with this. I was hoping you could help me with an
additional problem in calculating my totals. After successfully utilizing
your suggested calculation of =Sum(IIf([CreditRequest]=True,[Quantity]*[Unit
Price],0)), I realized that I also have a field called ForcedCredit Request
that needs to be included with the totals of CreditRequest.

I am not experienced with combining data in calculations and tried the
following:

=Sum(IIf([CreditRequest]=True,[Quantity]*[Unit Price],0) Or
(IIf([ForcedCreditRequest]=True,[Quantity]*[Unit Price],0)))

This didn't seem to work as the forced credits are only giving me totals of
$1.00 each. Is there a better way to add totals of records from two
different controls? Please advise.

Thanks,
Jim

you're welcome

Hi Tina,

Your assumption was correct and your suggested calculation worked perfectly.
Thank you for your assistance.

Sincerely,
Jim

assuming that the "checkbox" you refer to is actually a field with a Yes/No
data type, in the underlying table, try

=Sum(IIf([CreditRequest]=True,[Quantity]*[Unit Price],0))

hth


Hello,

I have a table that processes both credits and invoices, as needed, using
the same form. Now I need to create a monthly report that shows the total
credits and total invoices by product.

I tried to use an IF statement that would total all records with credits
(if
the credit checkbox was selected) and would total the invoices for all
records with the invoice checkbox selected. This did not give me an error
but does not give me a value. Please see the respective calculations
below:

=IIf([CreditRequest]="Yes",Sum([Quantity]*[Unit Price]),"")
=IIf([InvoiceRequest]="Yes",Sum([Quantity]*[Unit Price]),"")

The following formula will give me a total, by product, of both together
but
they need to be displayed separately.

=Sum([Quantity]*[Unit Price])

What am I doing wrong? Can someone please help me to make this work?

Thanks,
Jim

 

[tina]

since you want the same "then" and the same "else" results for both "if"
conditions, try combining the two conditions, as

=Sum(IIf([CreditRequest]=True Or [ForcedCreditRequest] =
True,[Quantity]*[Unit Price],0))

hth

Hi Tina,

Thanks again for you help with this. I was hoping you could help me with an
additional problem in calculating my totals. After successfully utilizing
your suggested calculation of =Sum(IIf([CreditRequest]=True,[Quantity]*[Unit
Price],0)), I realized that I also have a field called ForcedCredit Request
that needs to be included with the totals of CreditRequest.

I am not experienced with combining data in calculations and tried the
following:

=Sum(IIf([CreditRequest]=True,[Quantity]*[Unit Price],0) Or
(IIf([ForcedCreditRequest]=True,[Quantity]*[Unit Price],0)))

This didn't seem to work as the forced credits are only giving me totals of
$1.00 each. Is there a better way to add totals of records from two
different controls? Please advise.

Thanks,
Jim

you're welcome

Hi Tina,

Your assumption was correct and your suggested calculation worked perfectly.
Thank you for your assistance.

Sincerely,
Jim

:

assuming that the "checkbox" you refer to is actually a field with a Yes/No
data type, in the underlying table, try

=Sum(IIf([CreditRequest]=True,[Quantity]*[Unit Price],0))

hth


Hello,

I have a table that processes both credits and invoices, as

needed,
using

the same form. Now I need to create a monthly report that shows

the
total

credits and total invoices by product.

I tried to use an IF statement that would total all records with credits
(if
the credit checkbox was selected) and would total the invoices for all
records with the invoice checkbox selected. This did not give me

an
error

but does not give me a value. Please see the respective calculations
below:

=IIf([CreditRequest]="Yes",Sum([Quantity]*[Unit Price]),"")
=IIf([InvoiceRequest]="Yes",Sum([Quantity]*[Unit Price]),"")

The following formula will give me a total, by product, of both together
but
they need to be displayed separately.

=Sum([Quantity]*[Unit Price])

What am I doing wrong? Can someone please help me to make this work?

Thanks,
Jim

 

[Guest]

Hi Tina,

Thanks again! That did the trick.

Sincerely,
Jim

since you want the same "then" and the same "else" results for both "if"
conditions, try combining the two conditions, as

=Sum(IIf([CreditRequest]=True Or [ForcedCreditRequest] =
True,[Quantity]*[Unit Price],0))

hth

Hi Tina,

Thanks again for you help with this. I was hoping you could help me with an
additional problem in calculating my totals. After successfully utilizing
your suggested calculation of =Sum(IIf([CreditRequest]=True,[Quantity]*[Unit
Price],0)), I realized that I also have a field called ForcedCredit Request
that needs to be included with the totals of CreditRequest.

I am not experienced with combining data in calculations and tried the
following:

=Sum(IIf([CreditRequest]=True,[Quantity]*[Unit Price],0) Or
(IIf([ForcedCreditRequest]=True,[Quantity]*[Unit Price],0)))

This didn't seem to work as the forced credits are only giving me totals of
$1.00 each. Is there a better way to add totals of records from two
different controls? Please advise.

Thanks,
Jim

you're welcome


Hi Tina,

Your assumption was correct and your suggested calculation worked
perfectly.
Thank you for your assistance.

Sincerely,
Jim

:

assuming that the "checkbox" you refer to is actually a field with a
Yes/No
data type, in the underlying table, try

=Sum(IIf([CreditRequest]=True,[Quantity]*[Unit Price],0))

hth


Hello,

I have a table that processes both credits and invoices, as needed,
using
the same form. Now I need to create a monthly report that shows the
total
credits and total invoices by product.

I tried to use an IF statement that would total all records with
credits
(if
the credit checkbox was selected) and would total the invoices for all
records with the invoice checkbox selected. This did not give me an
error
but does not give me a value. Please see the respective calculations
below:

=IIf([CreditRequest]="Yes",Sum([Quantity]*[Unit Price]),"")
=IIf([InvoiceRequest]="Yes",Sum([Quantity]*[Unit Price]),"")

The following formula will give me a total, by product, of both
together
but
they need to be displayed separately.

=Sum([Quantity]*[Unit Price])

What am I doing wrong? Can someone please help me to make this work?

Thanks,
Jim

 

[tina]

you're welcome

Hi Tina,

Thanks again! That did the trick.

Sincerely,
Jim

since you want the same "then" and the same "else" results for both "if"
conditions, try combining the two conditions, as

=Sum(IIf([CreditRequest]=True Or [ForcedCreditRequest] =
True,[Quantity]*[Unit Price],0))

hth

Hi Tina,

Thanks again for you help with this. I was hoping you could help me

with
an

additional problem in calculating my totals. After successfully utilizing
your suggested calculation of =Sum(IIf([CreditRequest]=True,[Quantity]*[Unit
Price],0)), I realized that I also have a field called ForcedCredit Request
that needs to be included with the totals of CreditRequest.

I am not experienced with combining data in calculations and tried the
following:

=Sum(IIf([CreditRequest]=True,[Quantity]*[Unit Price],0) Or
(IIf([ForcedCreditRequest]=True,[Quantity]*[Unit Price],0)))

This didn't seem to work as the forced credits are only giving me

totals
of

$1.00 each. Is there a better way to add totals of records from two
different controls? Please advise.

Thanks,
Jim

:

you're welcome


Hi Tina,

Your assumption was correct and your suggested calculation worked
perfectly.
Thank you for your assistance.

Sincerely,
Jim

:

assuming that the "checkbox" you refer to is actually a field with a
Yes/No
data type, in the underlying table, try

=Sum(IIf([CreditRequest]=True,[Quantity]*[Unit Price],0))

hth


Hello,

I have a table that processes both credits and invoices, as needed,
using
the same form. Now I need to create a monthly report that

shows
the

total
credits and total invoices by product.

I tried to use an IF statement that would total all records with
credits
(if
the credit checkbox was selected) and would total the invoices

for
all

records with the invoice checkbox selected. This did not give

me
an

error
but does not give me a value. Please see the respective calculations
below:

=IIf([CreditRequest]="Yes",Sum([Quantity]*[Unit Price]),"")
=IIf([InvoiceRequest]="Yes",Sum([Quantity]*[Unit Price]),"")

The following formula will give me a total, by product, of both
together
but
they need to be displayed separately.

=Sum([Quantity]*[Unit Price])

What am I doing wrong? Can someone please help me to make

this
work?

Thanks,
Jim