Need some help with my DSUM function 1

[mrdod]

I haven't done this in a while so maybe what I'm trying to do can't be done the way I'm doing it so I need more brain power please. I'm trying to add the total hours worked for my employees for each pay period.

Here is my code
Dim stremployee As String
Dim dtmstart As Date
Dim dtmend As Date
Dim dbltotalhrs As Double
Dim dblreg As Double
Dim dblvac As Double
Dim dblsick As Double
Dim dblfloater As Double
Dim dblvto As Double
Dim dblother As Double

dtmstart = Nz(Format(Me.dtmstart.Value, "mm/dd/yyyy"))
dtmend = Nz(Format(Me.dtmend.Value, "mm/dd/yyyy"))
stremployee = Nz(Me.cbemployee.Value)

dbltotalhrs = DSum("[Hours Worked]", "tblemployeehistory", "[Employee Name] = """ & stremployee & """" And _
"[Date Worked]BETWEEN & #" & dtmstart & "# AND #" & dtmend & "#")


MsgBox dbltotalhrs

 

[PHV]

[tt]dbltotalhrs = DSum("[Hours Worked]", "tblemployeehistory", "[Employee Name]=[!]'[/!]" & stremployee & "[!]'[/!] AND [!]"[/!] _
[!] & [/!]"[Date Worked[!]] BETWEEN #[/!]" & dtmstart & "# And #" & dtmend & "#")[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mrdod]

PH, thanx for the response but I am getting an Invalid use of Null. The reason why I was using the double quote was I was concerned about names like "O'Connor". The names are separated by a comma ie Doe, John.

dbltotalhrs = DSum("[Hours Worked]", "tblemployeehistory", "[Employee Name]='" & stremployee & "' AND " _
& "[Date Worked] BETWEEN #" & dtmstart & "# And #" & dtmend & "#")

I did a copy and paste so what you corrected is what I used. Maybe I'm missing something?

 

[PHV]

You may try something like this:

Dim dbltotalhrs As Double
If Not (IsDate(Me!dtmstart) And IsDate(Me!dtmend)) Then
  MsgBox "blah blah about wrong pay period"
  Exit Sub
End If
If Trim(Me!cbemployee & "") = "" Then
  MsgBox "blah blah about wrong employee"
  Exit Sub
End If
dbltotalhrs = Nz(DSum("[Hours Worked]", "tblemployeehistory" _
  , "[Employee Name]='" & Replace(Me!cbemployee, "'", "''") & "' AND " _
  & "[Date Worked] BETWEEN #" & Format(Me!dtmstart, "yyyy-mm-dd") _
  & "# And #" & Format(Me!dtmend, "yyyy-mm-dd") & "#"), 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mrdod]

Never Mind!! I got to looking back at the formatting for my table and one thing that was wrong was the [Date Worked] field was text and not a date. Thanks PH!!