VBA DSum not working as I want..

[Badvoc]

I’m creating a database of time spent weekly on a project by an engineer.
I have a multiple items form “frmhrsweeklyinput “ with bound textboxes from a query "qryHrsWeeklyInput" in the detail section of the form ”task” “week1” “week2” “week3” “week4” “week5” then at the end I want to add these values together to give a total for January in an unbound textbox (M1). When the form is in form view it shows about 12 tasks in all.
Task is a text field, week1-5 are number fields
In the header I have a textbox with the “year” and command button with an “on click“ event to a select case in VBA.

Private Sub Command228_Click()
Select Case Me.Text214
Case "2010"
M1 = DSum("[Week1]+[Week2]+[Week3]+[Week4]+[Week5]", "qryHrsWeeklyInput", "[Projects]='" & Forms![ frmhrsweeklyinput]![Task] & "'")
Case "2011"
M1 = DSum("[Week1]+[Week2]+[Week3]+[Week4]", "qryHrsWeeklyInput", "[Project]='" & Forms![ frmhrsweeklyinput]![Task] & "'")
End select
End sub

The problem is that I get the same results for each task.
ie the result of week1-5 for task 1 shows in the results for task2 -12 regardless of what is in week1-5 for each task, does that make sense?
If I put the dsum in the control source of “M1” it works for all tasks but then I won’t have the select case for different years.

Im not an expert as you can see, just been asked to take an excel spreadsheet and put in access.

Cheers

 

[DCrake]

Firstly, in your query qryHrsWeeklyInput why not add a total field in there that is the value of each of the weekly totals. To contend with null values you will need to employ the Nz() function to eovercome this.

MonthlyTotal:Nz([Week1],0)+Nz([Week2],0)+Nz([Week3],0)+Nz([Week4],0)+Nz([Week5],0)

Then in your form

M1 = DSum("[MonthlyTotal]", "qryHrsWeeklyInput", "[Projects]='" & Forms![ frmhrsweeklyinput]![Task] & "'")

 

[Badvoc]

How would that work with the select case?
some months has 5 weeks and some 4 weeks, it's different every year.
I could do it with a form and query for each year, but Im sure theres a better way.

 

[DCrake]

I know that some months have 4 and 5 but the use of NZ() still enables you to add all fields together the fact that there is nothing in for that month will not affect the total.

Imagine if the company had a full week shut down over Xmas it may be that week 4 does not have any data.

 

[Badvoc]

Theres not a problem with adding the fields they all have "0" default.
the code works for the first record only, thats my problem.

 

[DCrake]

Would need to look at the whole section of code to analyse it for you.

 

[Badvoc]

Don't be to scaving..

 

[DCrake]

Where you get the impression that I was scaving is somewhat disconcerting. I was mearly attempting to look at the whole picture, not a small section, as this can prove to be more informative. But by the nature of your reply I hope someone else takes over this post as I do not take to being insulted for no particular reason.

 

[Badvoc]

thats not what I ment,
I ment dont be to scaving about the mess I'v made of the form, query.
Im sorry you feel I ment your previous replys.

 

[DCrake]

Appologies accepted, Now I am going to be scaving. Your structure is totally denormalised. You do not structure your data like that. What you need is a tabel for your engineers and a table your time recordings. Take a look on the site for examples. Also you do not store calculations in a table you express them in a query.

Also the design you have gone for is far to wide and seems to me you ar trying to emulate Excel. Remember Columns are expensive, Rows are free.

 

[Badvoc]

is that why the dsum is not working?