DSum in a Form (1 Viewer)

[mlh156]

I'm creating a Timesheet database and I'm having trouble with the time entry form as follows:

There is a main form called "F: Timesheet" with a sub-form called "F: Timesheet Detail" they have linked fields "Initials" and "Week Commencing" (a date field). The main form has just these two fields and a "Completed" check-box. The sub-form has a "Day" field ("Mon", "Tue" etc.) and a "Days" field (numerical - i.e. 1 day, 0.5 days etc), as well as fields for the project phase and task they worked on. Because there will be multiple tasks there may be more than one line for each day. At the bottom of the form (I don't really care if it's the main or sub-form), I want a total for each day, so users can see if they've allocated all their time for each day.

I've tried DSum in all sorts of ways but just keep getting an #Error message. Here's one attempt (eg. to sum Monday): =DSum("[Days]","F: Timesheet Detail","[Day]='Mon'").

At one stage, I thought it was working, when I pointed it to the underlying table, but it was summing all instances of "Mon" in the table, not just for the week in the timesheet. When I tried to restrict this as follows, I just got #Error again: =DSum("[Days]","Timesheet Detail","[Day]='Mon' And [Week Commencing]=forms![Timesheet]![Week Commencing]").

I'm afraid the syntax for DSum has me completely stumped. Any assistance would be greatly appreciated.

Note: I am using Access 2003

Cheers,

Marc

 

[mlh156]

After reading a few more of the links from this forum, I just tried the following. I now get an answer, but it equates to all the records in table "Timesheets", ignoring the criteria.

=DSum("[Days]","Timesheet Detail","[Day]= 'Mon'" And "[Week Commencing] = #" & Forms![F: Timesheets]![Week Commencing] & "#")

 

[pbaldy]

Presuming Week Commencing is actually a date value, try

=DSum("[Days]","Timesheet Detail","[Day]= 'Mon' And [Week Commencing] = #" & Forms![F: Timesheets]![Week Commencing] & "#")
Reply With Quote

 

[mlh156]

Presuming Week Commencing is actually a date value, try

=DSum("[Days]","Timesheet Detail","[Day]= 'Mon' And [Week Commencing] = #" & Forms![F: Timesheets]![Week Commencing] & "#")
Reply With Quote

Thanks Paul, that is definitely better but I still have a very puzzling issue:

The week I was initially testing with - 11/07/2009 - doesn't work (the DSum control remains blank), even when I delete all the records for that week and start from scratch. Similarly for 04/07/2009, 01/08/2009 and 05/09/2009. Many other weeks work perfectly and I can't see any pattern to which weeks work and which don't (except that all the dates I've found (so far) that don't work are in the first half of the month. No - scratch that; 03/10/2009 doesn't work but 10/10/2009 does).

I'm completely stumped. Any ideas anyone???

Thanks,

Marc

 

[pbaldy]

I wonder if it's a date format issue (mm/dd vs dd/mm).

 

[mlh156]

Hi again. Thanks for previous assistance on this. I never did get to the bottom of the inconsistent issue I was having above, but I have now significantly simplified my database by having only one time-recording table and a single form (no form/sub-form). I'll let my queries and reports summarise these by week-commencing date and days (Mon, Tue etc.)

However, I am still having issues with the DSum function. What it comes down to is that I just don't "get" the syntax. If possible I'd really appreciate:

1. Directions to a site or thread that explains the DSum syntax in detail

2. Help with my specific issue, as follows:

When the time entry form is opened, it first filters records to a given user and week commencing date. In the form footer, I would like to sum the time by weekday, based on the week_commencing date in the form header. Note: Form = [F:Time_Entry], Field to be summed = [Days], [Week_commencing] is a date and always a Saturday. [Week_commencing] is an unbound text box that just holds a date used to filter the records in the form.

So, for Monday's sum, I tried all manner of things, including: =DSum("[Days]","F:Time_Entry","[Date]=#[Week_commencing]+2#")

All I get is #Error

Any assistance would be much appreciated.

Marc

 

[pbaldy]

This explains the various syntax requirements:

http://www.mvps.org/access/general/gen0018.htm