dsum -

  • Thread starter Thread starter babs
  • Start date Start date
B

babs

New issue with DSUM - reposting haven't heard back - dsum works in
general(see below - or prev. post)

see 1st three lines of data below - they are the issue - showing tot.
vacation taken on 9/28,9/29, & 9/30 when not really taken until 10/1
Field Worker Job # Workdate Vacation Time (hours) VH TotVacTak
CAGNEY, KEVIN 90180056 9/28/2009 0 12

CAGNEY, KEVIN 90180056 9/29/2009 0 12

CAGNEY, KEVIN 90180056 9/30/2009 0 12

CAGNEY, KEVIN 90180056 10/1/2009 8 12

CAGNEY, KEVIN 90180056 10/2/2009 4 12

the 1st column of VH(vacation taken) shows the acutal date the vacation
taken. the second column is the DSUM- formula - text box (passed back in
based off the the dsum by man name and date within 2009) the total is
correct - BUT DO NOT LIKE that the dates prior to it taken(9/28-9/30/09) show
up as having used 12 hours of vacaction - anyway to add code in to it show a
Running Actual total -

Thanks sooo much for helping!
Barb


Previous posts- good but still above issue
In addition, if you wanted to restrict to specific dates, assuming the date
field is a field in "[JeffTime Card MD Query]", you could modify the WHERE
argument to include criteria on the date. Something like...

"[Man Name]= """ & [Man name] & """" & " AND [DateFieldName] Between
#1/1/09# AND #12/31/09#"
 
This is a case of 'you can not have your cake and eat it too.' DSum is Sum
of the Domain. It is not running total.

Try a subquery --
(SELECT Sum([XX].[Vacation Time]) FROM [YourTable] AS [XX] WHERE
[YourTable].[Field Worker] = [XX].[Field Worker] AND [XX].[Workdate]
<=[YourTable].[Workdate]) AS TotVacTak

--
Build a little, test a little.


babs said:
New issue with DSUM - reposting haven't heard back - dsum works in
general(see below - or prev. post)

see 1st three lines of data below - they are the issue - showing tot.
vacation taken on 9/28,9/29, & 9/30 when not really taken until 10/1
Field Worker Job # Workdate Vacation Time (hours) VH TotVacTak
CAGNEY, KEVIN 90180056 9/28/2009 0 12

CAGNEY, KEVIN 90180056 9/29/2009 0 12

CAGNEY, KEVIN 90180056 9/30/2009 0 12

CAGNEY, KEVIN 90180056 10/1/2009 8 12

CAGNEY, KEVIN 90180056 10/2/2009 4 12

the 1st column of VH(vacation taken) shows the acutal date the vacation
taken. the second column is the DSUM- formula - text box (passed back in
based off the the dsum by man name and date within 2009) the total is
correct - BUT DO NOT LIKE that the dates prior to it taken(9/28-9/30/09) show
up as having used 12 hours of vacaction - anyway to add code in to it show a
Running Actual total -

Thanks sooo much for helping!
Barb


Previous posts- good but still above issue
In addition, if you wanted to restrict to specific dates, assuming the date
field is a field in "[JeffTime Card MD Query]", you could modify the WHERE
argument to include criteria on the date. Something like...

"[Man Name]= """ & [Man name] & """" & " AND [DateFieldName] Between
#1/1/09# AND #12/31/09#"
--
Frank H
Rockford, IL



John Spencer said:
Assumption is that Man Name is a text field. If so, you need to include quote
marks as delimiters. Also, you have () in the first argument which is
supposed to the be the field (column) name and lack square brackets around the
query name.

=DSum("[HoursVacaCor]","[JeffTime Card MD Query]","[Man Name]= """ & [Man
name] & """")
Click to expand...
Click to expand...
 
Back
Top