Need to add values from one column depend upon another column

[ahwahneeliz]

On a form, I have a field for entering leave type class (PB, SE, etc). Two other columns (LECture and LAB) accept hours missed (1.30, 2.04, etc.) Currently the balance is displayed based on a simple formula (=Sum([Lec])+Sum([Lab])) in a form field with this =(-1*[Dates Absent Subform].[Form]![total lec]).
I need to get the balance to not include Lec and Lab amounts for class type PB. Essentially, I need to track the PB leave, but not deplete it from the accrued SE leave.

[zmbd]

You need to look at two functions:
IIF() - To handle the conditions for your fields
NZ() - Just to make sure you handle null values correctly.

Once you write your formula, if you are still having pronlems then post your code and we'll help tweek it from there

[ahwahneeliz]

I tried the IIF in the formula described above, using skip if PB, and tried a couple other combos, not really getting what I want. I know I'm close, just something little is blocking.

Maybe my question wasn't posed quite right. How would you modify(=Sum([Lec])+Sum([Lab]) to select only values in the Lec and Lab, for a criteria in another column/field (not add or subtract if other was PB).

[zmbd]

The question is mostly clear...

Would you please post your formulas that you've attempted... I'm sure it's a simple fix.

Normally, it is a typo or the value in the field/control isn't what you think it is...


[(as for what one thinks a control is returning but it isn't doing so:
For example: A combobox can have one hidden column and one (or more) shown columns. The hidden column is the bound column to the control (that's the value that the control returns) say in your case you have PB, SE, etc in the shown column; however, in the bound column you might have, 1, 2, etc... If you then test for the CBO="PB" that will fail, however if you test fo CBO=1 for when you want "PB" then that will pass.
Many people fall into this trap because they use the wizard and don't understand what is being created for them]

[ahwahneeliz]

if I use =+([Lec])+([Lab]) I only get the balance for the particular leave date. If I use =Sum([Lec])+Sum([Lab]) I get the total of both columns.
If I use =iif(class="PB" ,skip,Sum([Lec])+Sum([Lab])) I get #Type! (which I expect since its summing columns). I used =IIf([class]="SE",+[Lec])+([Lab], I get results in the balance field only for the record I'm in. I'm sorry, I'm not sure of the correct terms, I have a table for dates/reasons, a table for the names/personal info, and other tables. The form displays the table of dates/reasons for each particular instructor, linked on social security field. The balance formula is only in the form, I don't try to hold a balance in any tables. It's fine for only accruing and depleting sick leave only, but now I need to track but not accrue or deplete the PB from the balances.