How to add negative with DSUM 1

[MICKI0220]

I need to some assistance in what I believe is a query using DSUM. I have to create a Bonus report using that query. The report will show what the negative carryover is. For example. Each month a worker gets a bonus based on saved hours. It is possible for them to get a negative bonus at which point this goes into the negative carryover field. This is not a problem for the first month. However they must now work themselves out of the negative. So, If they get a bonus the next month, the allocated bonus field will be populated with the bonus, but the negative carryover must now subtract the bonus or add to the negative if they still didn't earn a bonus. Once they have paid all the negative back by earned bonus, the negative should show zero instead of - a number. Please point me in the right direction. I have looked at the DSUM in the knowledge base and I don't get it.

 

[PaulBricker]

Do you actually have two fields you are trying to calculate? Apparently you have a negative carryover field. Do you have a bonus field as well? Can you create a calculated field that adds the two fields together?

If you are trying to add two fields together using DSum the syntax would be

DSum("Field1 + Field2","TableName",Criteria)

Paul

 

[MICKI0220]

Yes I actually have two fields. One is called Allocated Bonus. The other is Negative CArryover. I need both fields to change based on what the allocated bonus is. Say that in January an employee recieved -212.00 as a bonus. this field then becomes the negative carryover. And Allocated becomes zero. The next month he recieves +100.00 bonus. The negative carryover field would need to then change to -112.00 . And the allocated would go to zero. The next month he gets +300.00 bonus. At this point the negative carryover needs to go to zero and the allocated bonus go to 188.00. Does this make sense?

 

[PaulBricker]

First, I would look at using the Running Sum value in your report to get the fields to sum by month. That will ultimately work better than the DSum.
Second, do you need to actually do the calculations in a query?
Let me know.


Paul

 

[MICKI0220]

No, I don't actually have to do it in a query, I am looking into writing a procedure, but haven't had luck with that yet either.

 

[PaulBricker]

The running sum property in reports will probably give you the data in format you are looking for.
Depending on how you want the data displayed, you would start with an employee group header with the group on property set to "each value". Then add a date group header inside the employee group. Set the Group On property to "Month". Add your date field to that header. Then in the Detail section add your two data fields, allocated bonus and Negative Carryover. Set the Running Sum property to 'over group'(I think. You might need to set it to 'over all'.
Then in the Employee group footer, add two textboxes.
I'll call them txtAllocatedSum, txtNegativeSum
txtAllocatedSum will have a Control source set to an expression like this
=IIf([Allocated Bonus] + [Negative Carryover]<=0,0,[Allocated Bonus])

txtNegativeSum will have an expression like this.
=IIf([Allocated Bonus] + [Negative Carryover])>=0,0,[Negative Carryover])

I think this will get you close. Try it out and post back with any problems you run into. I'll keep an eye out over the weekend.

Paul