Counting Dates

[gregorg]

Hi I have data

1/1/2000 : aTime : Text
1/1/2000 : aTime : Text
2/1/2000 : aTime : Text
3/1/2000 : aTime : Text

I need to return the sum of fields that have the same date.

so, (2,1,1) as there are two entries for 1/1/2000, 1 for 2/1/2000 and 1 for 3/1/2000

can anyone help ?

Gregor.

 

[raskew]

Hi -

Here's an example of a query of a table with a date. To use, replace table/field name(s) with those from your table.

SELECT
    tblPayments.dteLastPaid
  , Count(tblPayments.dteLastPaid) AS CountOfdteLastPaid
FROM
   tblPayments
GROUP BY
   tblPayments.dteLastPaid
HAVING
   ((Not (tblPayments.dteLastPaid) Is Null))
ORDER BY
   tblPayments.dteLastPaid;

HTH - Bob

 

[gregorg]

its a bit difficult because to dateof fields is date and time and i need to pick out the same date only, so "1/1/2009 18:00:00" and "1/1/2009 18:03:11" would sum together. ..

 

[raskew]

Hi -

You can use the DateValue() function to return just the date. Example:

x = now()
? x
3/3/2009 7:10:16 AM
? datevalue(x)
3/3/2009

Try that with each of your date fields.

Bob

 

[gregorg]

hi can u say where datevalue would go into the SQL ? not used to sql..

 

[Dennisk]

Any function in the VBA library or a user written function that's public can be used in a query

SELECT DateValue(myTime) as DateOnly FROM YourTable