DSum working but not working .... really strange

[Arvin]

Okay .... I finally got this work to with the help of boblarson .... but its acting up and I have no idea ...

I am using the following ...

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If (Me.PlannedHours) <= 0 Or IsNull(Me.SubActivityDetails) Then
      MsgBox ("You must enter your planned hours and provide a description of the activity"), 0, "Weekly Schedule Control System"
      Cancel = True
  End If
  If DSum("[PlannedHours]", "EmployeeWeeklyActivityCommentstbl", "[CommentsDateTimeStamp] Between ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) - 7 And ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) = " & Me.CurRptWeekendDatetxt + Me.PlannedHours < 34.15) Then
          MsgBox ("Your time entry has been saved for the current weekending report " & Format(Forms!AddActivityCommentsfrm!CurRptWeekendDatetxt, "long date")) & vbCrLf & vbCrLf & ("Currently, you have planned for " & Forms!Mainfrm!EmployeeDashboardSubfrm!SumOfPlannedHours & " Hrs"), 0, "Weekly Schedule Control System"
      Cancel = False
  End If
   If DSum("[PlannedHours]", "EmployeeWeeklyActivityCommentstbl", "[CommentsDateTimeStamp] Between ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) - 7 And ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) = " & Me.CurRptWeekendDatetxt + Me.PlannedHours > 34.15) Then
          MsgBox ("You have exceeded your planned hours limit of 34.15 for the current weekending report " & Format(Forms!AddActivityCommentsfrm!CurRptWeekendDatetxt, "long date")) & vbCrLf & vbCrLf & ("Currently, you have planned for " & Forms!Mainfrm!EmployeeDashboardSubfrm!SumOfPlannedHours & " Hrs"), 0, "Weekly Schedule Control System"
      Cancel = True
  End If
End Sub

For some reason .... I cannot add any more time ... I have verify data in the tables ... I and I only have 2.00 hrs logged .. and when I go to enter more time ... the vba kicks and states that I have exceeded when in fact I only have 2.00 hrs of time for the current weekending date ....

Any ideas ...please and thank you in advance

 

[Arvin]

***Note***

the only thing that comes to my mind is .... that I am storing the Planned and Actual hrs as "numbers" in the table and the feild size is "Single" and format is "Fixed" ....

Just wondering ... should a store the hours as "text" and just format them in a query later?

 

[Arvin]

Just adding a screenshot so ppl don't think I'm crazy ....

All I want is for the code to do ....

check if the person over or under and allocated time (34.15) for the current weekending and display the appropriate message .... thats all ....

why it is not working is beyond me

 

[Arvin]

Just tried using the following and now I'm getting a Run time error 94

Invalid use of Null

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Result As Double
  If (Me.PlannedHours) <= 0 Or IsNull(Me.SubActivityDetails) Then
      MsgBox ("You must enter planned hours and provide a description"), 0, "Weekly Schedule Control System"
      Cancel = True
  End If
 Result = DSum("[PlannedHours]", "EmployeeWeeklyActivityCommentstbl", "[CommentsDateTimeStamp] Between ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) - 7 And ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) = " & Me.CurRptWeekendDatetxt + Me.PlannedHours)
 If Result > 34.15 Then
 MsgBox ("You have exceeded your max of 34.15 Hrs for the weekending " & Format(Forms!AddActivityCommentsfrm!CurRptWeekendDatetxt, "long date")) & vbCrLf & vbCrLf & ("Currently, you have planned for " & Forms!Mainfrm!EmployeeDashboardSubfrm!SumOfPlannedHours & " Hrs"), 0, "Weekly Schedule Control System"
Cancel = True
End If
If Result < 34.15 Then
MsgBox ("Your entry has been saved for the weekending " & Format(Forms!AddActivityCommentsfrm!CurRptWeekendDatetxt, "long date")) & vbCrLf & vbCrLf & ("Currently, you have planned for " & Forms!Mainfrm!EmployeeDashboardSubfrm!SumOfPlannedHours & " Hrs"), 0, "Weekly Schedule Control System"
Cancel = False
End If
End Sub

i don't get it ... any help is very appreciated ...thank you in advance