writing formula for Access

[Levi]

New at this. I am having trouble writing my formula (invalid syntex) to
get sum of one field if another field is ="credit"

=Sum([Total_Paid])IIf([MoneyRec]="Credit",0)))

And then I Need to another formula to subtract $700 from the sum for a
net total.
How would I do that?

Can someone help me?

 

[fredg]

New at this. I am having trouble writing my formula (invalid syntex) to
get sum of one field if another field is ="credit"

=Sum([Total_Paid])IIf([MoneyRec]="Credit",0)))

And then I Need to another formula to subtract $700 from the sum for a
net total.
How would I do that?

Can someone help me?

=Sum(IIf([MoneyRec] = "Credit",[Total_Paid],0))

Besides the incorrect syntax you used, it is always good practice to
count your open parenthesis and subtract the close parenthesis in an
expression. If the result is not 0 you'll have a problem. Your
expression had 2 open and 4 close parenthesis. Not good. :-(

For the second question, try:
=Sum(IIf([MoneyRec] = "Credit",[Total_Paid],0)) - 700

 

[Levi]

Thanks Fred for responding. Trouble is, I have no idea what you were
saying on the first.Can you help show me how to write as you did on my
second question. Sorry - I'm just learning. Have had no training.

New at this. I am having trouble writing my formula (invalid syntex) to
get sum of one field if another field is ="credit". Wrote it as:

=Sum([Total_Paid])IIf([MoneyRec]="Credit",0)))

And then I Need to another formula to subtract $700 from the sum for a
net total.
How would I do that?

Can someone help me?

=Sum(IIf([MoneyRec] = "Credit",[Total_Paid],0))

Besides the incorrect syntax you used, it is always good practice to
count your open parenthesis and subtract the close parenthesis in an
expression. If the result is not 0 you'll have a problem. Your
expression had 2 open and 4 close parenthesis. Not good. :-(

For the second question, try:
=Sum(IIf([MoneyRec] = "Credit",[Total_Paid],0)) - 700

 

[Ed Warren]

I find it helpful to put the logic stuff into simple sentences then put them
into code.
Example for your first statement:

If AnotherField is equal to the value "credit" then
return the sum of the total paid field
Otherwise
return the value 0

So now to convert this into code:

Using the IIF statement in a query::

Note: The Nz function is added to handle the cases where the value of
[Total_Paid] is null or for some reason cannot be evaluated.

=IIF([AnotherField] = "Credit",Sum(Nz([Total_Paid]),0),0)

Ed Warren

Thanks Fred for responding. Trouble is, I have no idea what you were
saying on the first.Can you help show me how to write as you did on my
second question. Sorry - I'm just learning. Have had no training.

New at this. I am having trouble writing my formula (invalid syntex) to
get sum of one field if another field is ="credit". Wrote it as:

=Sum([Total_Paid])IIf([MoneyRec]="Credit",0)))

And then I Need to another formula to subtract $700 from the sum for a
net total.
How would I do that?

Can someone help me?

=Sum(IIf([MoneyRec] = "Credit",[Total_Paid],0))

Besides the incorrect syntax you used, it is always good practice to
count your open parenthesis and subtract the close parenthesis in an
expression. If the result is not 0 you'll have a problem. Your
expression had 2 open and 4 close parenthesis. Not good. :-(

For the second question, try:
=Sum(IIf([MoneyRec] = "Credit",[Total_Paid],0)) - 700

 

[fredg]

Thanks Fred for responding. Trouble is, I have no idea what you were
saying on the first.Can you help show me how to write as you did on my
second question. Sorry - I'm just learning. Have had no training.

New at this. I am having trouble writing my formula (invalid syntex) to
get sum of one field if another field is ="credit". Wrote it as:

=Sum([Total_Paid])IIf([MoneyRec]="Credit",0)))

And then I Need to another formula to subtract $700 from the sum for a
net total.
How would I do that?

Can someone help me?

=Sum(IIf([MoneyRec] = "Credit",[Total_Paid],0))

Besides the incorrect syntax you used, it is always good practice to
count your open parenthesis and subtract the close parenthesis in an
expression. If the result is not 0 you'll have a problem. Your
expression had 2 open and 4 close parenthesis. Not good. :-(

For the second question, try:
=Sum(IIf([MoneyRec] = "Credit",[Total_Paid],0)) - 700

I don't understand.
I gave you the full expression you need in my reply:

=Sum(IIf([MoneyRec] = "Credit",[Total_Paid],0))

Also make sure the name of this control is not the same as the name
of any field used in it's expression. In other words, do not name this
control "MoneyRec" nor "Total_Paid".

 

[Levi]

Thanks - you are so kind.

I find it helpful to put the logic stuff into simple sentences then put them
into code.
Example for your first statement:

If AnotherField is equal to the value "credit" then
return the sum of the total paid field
Otherwise
return the value 0

So now to convert this into code:

Using the IIF statement in a query::

Note: The Nz function is added to handle the cases where the value of
[Total_Paid] is null or for some reason cannot be evaluated.

=IIF([AnotherField] = "Credit",Sum(Nz([Total_Paid]),0),0)

Ed Warren

Thanks Fred for responding. Trouble is, I have no idea what you were
saying on the first.Can you help show me how to write as you did on my
second question. Sorry - I'm just learning. Have had no training.

On 9 Feb 2006 19:12:17 -0800, Levi wrote:

New at this. I am having trouble writing my formula (invalid syntex) to
get sum of one field if another field is ="credit". Wrote it as:

=Sum([Total_Paid])IIf([MoneyRec]="Credit",0)))

And then I Need to another formula to subtract $700 from the sum for a
net total.
How would I do that?

Can someone help me?

=Sum(IIf([MoneyRec] = "Credit",[Total_Paid],0))

Besides the incorrect syntax you used, it is always good practice to
count your open parenthesis and subtract the close parenthesis in an
expression. If the result is not 0 you'll have a problem. Your
expression had 2 open and 4 close parenthesis. Not good. :-(

For the second question, try:
=Sum(IIf([MoneyRec] = "Credit",[Total_Paid],0)) - 700