If Then in a table update 1

[MICKI0220]

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

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

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

 

[PHV]

You may consider the IIf function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

And I would re-write your IF statement:
[tt]
If CommissionRemaining.value > CommissionEarnedThisPeriod than [red]
CommissionEarnedThisPeriod.value = CommissionEarnedThisPeriod.value[/red]
Else
CommissionEarnedThisPeriod.value = CommissionRemaining.value
End If[/tt]

To something like:
[tt]
If CommissionRemaining.value <= CommissionEarnedThisPeriod than
CommissionEarnedThisPeriod.value = CommissionRemaining.value
End If[/tt]

Have fun.

---- Andy

 

[MICKI0220]

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.

 

[PHV]

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MICKI0220]

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

DoCmd.RunSQL "UPDATE Projects SET Projects.ResultingCommissionPeriodReg = IIf([Projects]![ResultingCommissionPeriodReg]>[Projects]![TotalComRemaining],[Projects]![TotalComRemaining],[Projects]![ResultingCommissionPeriodReg])"

Thanks again for the input.

 

[PHV]

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
FAQ181-2886

 

[MICKI0220]

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.

 

[PHV]

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
FAQ181-2886

 

[MICKI0220]

I got it to work. Thank you.

 

[PHV]

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

 

[MICKI0220]

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.