Totals of entries in a date field

[jcbhydro]

I have a simple table named [Groups] containing a Primary key ID, a Group name, a Group Leaders name and a date field. The date field merely lists the date of a particular Group related occurrence.
I need to know how many records have dates entered and assumed a simple total would achieve this. The Total is correctly shown in the appropriate row but most, but not all, of the date records are converted to show the same 'total' number.eg the date 28/01/2014 is changed to !4. Quite a weird occurrence.

Is it the case that date records cannot be totaled, in which case, why not all?

Any suggestions would be welcome.

jcbhydro

 

[namliam]

You cannot sum dates if you are asking that...
You can group by them

 

[CazB]

you may COUNT dates, but not sum them....

 

[MarkK]

Yes, you can sum dates. Here is an excellent way to calculate working hours for an employee for the week given his punch in and out date/time values . . .

Private Sub Test1267419764()
    Dim d As Date
    
    d = _
    -1 * #1/27/2014 6:00:00 PM# _
    + 1 * #1/27/2014 9:00:00 PM# _
    - 1 * #1/27/2014 10:00:00 PM# _
    + 1 * #1/28/2014 3:00:00 AM# _
    - 1 * #1/28/2014 6:00:00 PM# _
    + 1 * #1/28/2014 9:00:00 PM#
    
    Debug.Print CSng(d * 24) & " hours total"
    
End Sub

Assume there is a field called PunchDirection, which is -1 for punching in and +1 for punching out, and you multiply that with the date/time, and then do the sum, you get a very simple way to calculate total hours. I was just going over this in another thread.

 

[namliam]

Yes you can sum WORKING HOURS or times in general, which yes is a date field but not quite a date perse, but is a result of substracting two dates....
You cant sum DATES and expect legible answers
To inkeep with your example...

Private Sub Test1267419764()
    Dim d As Date
    
    d = _
    +1 * #1/27/2014 6:00:00 PM# _
    + 1 * #1/27/2014 9:00:00 PM# _
    + 1 * #1/27/2014 10:00:00 PM# _
    + 1 * #1/28/2014 3:00:00 AM# _
    + 1 * #1/28/2014 6:00:00 PM# _
    + 1 * #1/28/2014 9:00:00 PM#
    
    Debug.Print CSng(d * 24) & " hours total"
    
End Sub

Which would be the equivalent of using SUM in a query.