DSum Problem Between 2 dates

[Badeye]

Hi Guys,

Can't get my head round this at all and was wondering if someone could shine some light on my problem

I have 2 table, 'Employees' & 'QHrsWorked'

The employee table has columns: Empno, Cont_Hrs1, Start_Date1, Cont_Hrs2, Start_Date2, Hols_Yr_End_Date
The QHrsWorked Table has columns: Empno, Hrs_Worked, PE_Date

Both Tables are linked by Empno in a query. What i'm wanting to do in the query is pull out the total hours worked between 2 dates (Start_Date1 & Start_Date2 or Hols_Yr_End_Date).

Below is what i have at the moment, if i put the second date in as [Hols_Yr_End_Date] the formula appears to work ok, if i change the second date to [Start_Date2] all i get is #Error

Any help would be appreciated.

Formula:

ROI_Worker1: IIf([Cont_Hrs2] Is Not Null,NZ(DSum("Hrs_Worked","QHrsWorked","[PE_Date] Between #" & Format([Start_Date1],"mm/dd/yyyy") & "# And #" & Format([Start_Date2],"mm/dd/yyy") & "#"),0))

 

[jdraw]

Normalize your Tables.

What do these represent, and why are they in the Employee table?
Cont_Hrs1, Start_Date1, Cont_Hrs2, Start_Date2, Hols_Yr_End_Date

Sounds like you need 2 tables (at least)

Employee
EmpId
EmpFName
EmpLName
...

EmpHoursWorked
WorkRecId PK
EmpId (FK to Employee.EmpId)
WorkStartDateTime
WorkEndDateTime
WorkType<---Cont vs Hol???? Not sure what these mean exactly


You can calculate the HoursWorked from the workStart and workEnd

 

[Badeye]

Cheers bud, i'm not sure what was going wrong with my DSum but i built my query again and now it works fine.
I'm still very new at this and learning ways to do things.

Cheers,

Si

 

[jdraw]

Glad you got it working.