DSum not linking up to EmployeeName form

[pampers4u]

Whenever I click on an employee name, I need to have the running total Balance calculated only for the employee selected in the AnnualLeave subform of the Employee form, and not to all the other employee names as is the case now.
The formula I used to calculate the running balance is:
=DSum("Nz([CarriedOver], 0)+Nz([Earned], 0)-Nz([Used], 0)","[AnnualLeave]")

The formula I tried to use to restrict the running balance to the EmployeeName on the main form is:


=DSum("Nz([CarriedOver], 0)+Nz([Earned], 0)-Nz([Used], 0)","[AnnualLeave]","EmployeeName='George'")

But for some reason I'm getting a "#Error" message in the Balance column of the AnnualLeave subform.

Please help!
Thanks, Chris

[June7]

1. I suggest a unique ID for the PayPeriod2011 table. The autonumber datatype would serve or you could structure a unique value, like 1101, 1102, etc. Retrieve the pay period dates on forms by join tables in form RecordSource query. Is this db going to serve beyond 2011?
SELECT AnnualLeave.*, PayPeriod2011.PayPeriodEnding
FROM PayPeriod2011 RIGHT JOIN AnnualLeave ON PayPeriod2011.PPID = AnnualLeave.PayPeriod;

2. I suggest not using SSN for employee unique ID. Use another unique identifier as key. Again, the autonumber could serve.

3. The Balance and Carried Over fields in Employee are not needed. Calculated value like this should not be saved to table. It can be generated when needed. Access 2010 does have a new field datatype called Calculated. This is the only way you should have these fields set up if you really want it. If you need to show value for employee leave balance to start the database, enter dummy pay period records (pay period 0). The amount carried forward to the next pay period should be calculated.
=DSum("Nz([Earned], 0)-Nz([Used], 0)","AnnualLeave","EmpID=" & [EmpID] & " AND PayPeriod<" & [Payperiod])
Then calculate Balance with: =Nz([CarriedOver],0)+Nz([Earned],0)-nz([Used],0)

4. What is the determination for UseOrLose?