Using DSum To Display Running Total On Form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff Garrison - 21 May 2008 18:26 GMT

I have a form that tracks vacation hours accrued and taken. The Fields are
as follows:

Week Period End Date Accrued Hours Hours Taken Balance
1 12/26/07 3.08 0.0
2 1/9/08 3.08 0.0
3 1/23/08 3.08 0.0
4 2/6/08 3.08 8.0

Since I don't want to store a calculated field in my table, I need to have
the Balance field total the number of Accrued Hours less the Hours Taken for
each week. My thinking is to use DSum to get the info, but based on the
statement I'm using below, I'm getting the same value (3.08) as the balance
for each week

vba statement:
Balance = DSum("[vacationAccrual]", "tblEmployeeVacation", "[vacationWeek]
<= " & [vacationWeek] & " And [vacationEmployeeNumber]= " &
[vacationEmployeeNumber] & " And [vacationYear]= " & [vacationYear] & "")

tblEmployeeVacation is the table that stores the Accrued Hours and Hours
Taken
vacationYear is the Year, taken from the form
vacationEmployeeNumber is the Employee Number taken from the form as well.

Am I missing something obvious?

Thanks.

Jeff

I have a form that tracks vacation hours accrued and taken. The Fields are
as follows:

Week Period End Date Accrued Hours Hours Taken Balance
1 12/26/07 3.08 0.0
2 1/9/08 3.08 0.0
3 1/23/08 3.08 0.0
4 2/6/08 3.08 8.0

Since I don't want to store a calculated field in my table, I need to have
the Balance field total the number of Accrued Hours less the Hours Taken for
each week. My thinking is to use DSum to get the info, but based on the
statement I'm using below, I'm getting the same value (3.08) as the balance
for each week

vba statement:
Balance = DSum("[vacationAccrual]", "tblEmployeeVacation", "[vacationWeek]
<= " & [vacationWeek] & " And [vacationEmployeeNumber]= " &
[vacationEmployeeNumber] & " And [vacationYear]= " & [vacationYear] & "")

tblEmployeeVacation is the table that stores the Accrued Hours and Hours
Taken
vacationYear is the Year, taken from the form
vacationEmployeeNumber is the Employee Number taken from the form as well.

Am I missing something obvious?

Thanks.

Jeff

Reply to this Message

SC in Texas - 21 May 2008 22:18 GMT

Jeff:
Are you trying to show a running balance of accrued to date - used to date
for each week.
Steve

> I have a form that tracks vacation hours accrued and taken. The Fields are
> as follows:
[quoted text clipped - 57 lines]
>
> Jeff

Reply to this Message

SC in Texas - 21 May 2008 22:42 GMT

Jeff,
Another question are you wanting to manipulate the data in the form or are
you just looking to see what it shows. If you are just looking to see what
has occurred, use a report instead of a form. Add a second text box,
txtboxA, whose data source is the vacation accrued field and change it's
running sum property from no to overall or over group. Do the same with a
second text box, txtboxB, whose data source is the vacation used. Now create
a third text box whose date source is "= txtboxA - txtboxB" with out the
quotes.

You can then preview or print the report.

Good Luck,
Steve Callahan

> I have a form that tracks vacation hours accrued and taken. The Fields are
> as follows:
[quoted text clipped - 57 lines]
>
> Jeff

Reply to this Message