Why cant Access count correctly??

  • Thread starter Thread starter Stan
  • Start date Start date
S

Stan

I have a simple database that tracks telephone calls. At the end of the
month I use a simple query to count the number of calls that have been
received during the month by:-
SELECT Count(Call_Log.Call_Start) AS CountOfCall_Start
FROM Call_Log
WHERE (((Call_Log.Call_Date) Between #10/1/2004# And #10/31/2004#));
This gives the result 674 calls.
However if I either physically count each row in the data set or highlight
and paste them into Excel the count comes to 635 calls!!!!!
Have tried using the Call_Date for the count and also different dates but
there still seems to be a discrepancy between the computed result and hand
counting them.
Am I going mad????

Any sane answer would be very much appreciated......

MTIA

Stan
 
Stan said:
I have a simple database that tracks telephone calls. At the end of
the month I use a simple query to count the number of calls that have
been received during the month by:-
SELECT Count(Call_Log.Call_Start) AS CountOfCall_Start
FROM Call_Log
WHERE (((Call_Log.Call_Date) Between #10/1/2004# And #10/31/2004#));
This gives the result 674 calls.
However if I either physically count each row in the data set or
highlight and paste them into Excel the count comes to 635 calls!!!!!
Have tried using the Call_Date for the count and also different dates
but there still seems to be a discrepancy between the computed result
and hand counting them.
Am I going mad????

Any sane answer would be very much appreciated......
Click to expand...

You do know that BETWEEN is inclusive right? That it will include calls
that occur on your start date and on your end date?
 
Rick Brandt said:
You do know that BETWEEN is inclusive right? That it will include calls
that occur on your start date and on your end date?
Click to expand...

Actually, if the Call_Date also includes time, the resultant recordset won't
include any calls for 10/31/2004. It will, of course, pick up all of them
for 10/1/2004.

What is Call_Log, Stan. Is it a table or a query? If a query, pasting the
SQL here might help.
 
Hi Douglas,
Thanks for coming back to me so quickly -
Call_Log is the name for the table.
Had hoped that BETWEEN would include the calls for the first date and end
date - but not sure what you meant by "if call_date contained a time the
recordset would not include any calls for 10.31.2004". The call_date is the
date put in from Access - Field is formatted as Short Date, Input mask
99/99/00;0 with default value Date() .

Cheers for any advice

Stan
 
Douglas said:
Actually, if the Call_Date also includes time, the resultant
recordset won't include any calls for 10/31/2004. It will, of course,
pick up all of them for 10/1/2004.
Click to expand...

Understood, but since he is complaining about seeing *more* records than he
expects I figured he might not not know about the inclusiveness of BETWEEN.
When people are bitten by the Time component issue they see *fewer* records
than expected.
 
Stan said:
Hi Douglas,
Thanks for coming back to me so quickly -
Call_Log is the name for the table.
Had hoped that BETWEEN would include the calls for the first date and
end date - but not sure what you meant by "if call_date contained a
time the recordset would not include any calls for 10.31.2004". The
call_date is the date put in from Access - Field is formatted as
Short Date, Input mask 99/99/00;0 with default value Date() .

Cheers for any advice
Click to expand...

When you filter using Dates only you are in effect filtering for records
having time of exactly midnight on the "end date". If your records include
a time you will not get anything past midnight on the end date. The usual
work-around is to add one day to the end date.

How you are formatting your fields for display doesn't enter into it.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What Doug was getting at is that, if there are time values included in
the date column then using the date 10/31/2004 will not include calls
that were made on 10/31/2004 starting at 1 second past midnight to 1
second before 11/1/2004.

Your original criteria is really like this:

BETWEEN #10/1/2004 00:00:00# And #10/31/2004 00:00:00#

If you are expecting to count calls made on 10/31 your date criteria
should be like this (if your date column is really storing the time
value):

BETWEEN #10/1/2004# And #10/31/2004 23:59:59#

The way to check if the Time value is stored with your Date value is a
query like this:

SELECT Format(date_column, "m/d/yyyy hh:nn:ss") FROM table_name

If you get ALL dates like this:

10/1/2004 00:00:00
10/2/2004 00:00:00

always zeros in the hours:minutes:seconds part, then the time part of
the date is not being stored.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQdsuAoechKqOuFEgEQLFqQCcC1AMNC1C/LJxLi0LLhir+kWVEVYAoPDB
X4S0pFrCe5cHTgPoXIE6zXRB
=YbAm
-----END PGP SIGNATURE-----
 
Thanks Mike,
When I run the query you suggest to check, I receive the dates with the
times as all the 00:00:00 following each one - thus presume this means dates
are not including the time stamp.
So should this mean that with having the criteria as "BETWEEN 01/12/2004 AND
31/12/2004" the calls for the 31/12 00:01 ~ 23:59 will be included??
I think my biggest problem at the moment is trying to get my head around the
why the computed result is higher than the actual number of rows in the
table!!!
Am now starting on running the total for each day and then counting them -
so far my results indicate that the computed result and my hand-counted
result are exactly the same over 5 days so there has got to be something
weird towards the end of the month!!! Hopefully I'll trip over it!
Will let you all know the outcome

Many thankx

Stan
 
Queries use the American format for dates. Try
Between 12/1/2004 and 12/31/2004
 
Back
Top