I am having a brain lockup. I have been writing this program using alot of sql updates to make a work table. Right now I am coming into a problem with a calculation on commissions. Here is my problem. I have a commission database that salesmen recieve a commission when a payment comes in on a contract. However it is only for maybe two years of a ten year commission that they get commission. So the field CommissionRemaining, may have 20 dollars left, but the calculation on commission this period may be 50 dollars. I need to code a statement on the table that changes the commissionthis period to be only what is left. So my statement is this in pseudocode

I hope I have explained it correctly. I am assuming that because I am not using a query that I will need to link the table with a dlookup. My primary key is JOBID. This code would need to do this for all records in the table.

Thank you for any assistance with this.

Micki

You may consider the IIf function.

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

And I would re-write your IF statement:

If CommissionRemaining.value > CommissionEarnedThisPeriod than
CommissionEarnedThisPeriod.value = CommissionEarnedThisPeriod.value
Else
CommissionEarnedThisPeriod.value = CommissionRemaining.value
End If

To something like:

If CommissionRemaining.value <= CommissionEarnedThisPeriod than
CommissionEarnedThisPeriod.value = CommissionRemaining.value
End If

Have fun.

---- Andy

Phv
Can I put that whole if statement in an IIf function in an sql statement If have used the IIf statements for things like if the value is less than >0, 0.

The syntax is:
=IIf(testexpression, truevalue, falsevalue)

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

This is what I am using and instead of taking the value of it, it puts a zero in its value.


Thanks again for the input.

Seems like Projects.ResultingCommissionPeriodReg is already set to zero before the update ...
instead of taking the value of it
The value of what ?

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

it's not set to zero. I keep running the code piece by piece by commenting it out and right before this part runs, the field has 2900.00 in it. I just meant the value as in 2900.00.

What about this ?
DoCmd.RunSQL "UPDATE Projects SET ResultingCommissionPeriodReg=TotalComRemaining WHERE ResultingCommissionPeriodReg>TotalComRemaining"

the field has 2900.00 in it
In the table ?

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

I got it to work. Thank you.

I got it to work
Please, share your working solution.

The problem was that one of the records I was using to verify things were working had a null value in the total commission remaining field. Once it had a value it worked like a charm.