DCOUNT criteria with dates

[Bill]

I have scoured the forums on this and realise that it can be tricky.

I am retrospectively counting callogs raised each day on a date criteria and
mostly it works fine, but every now and again it returns a block of zero
values even though there is matching criteria.

I have looked and looked and cannot spot the problem. There don't appear to
be any nulls that could be messing things up, VarType indicates all the
dates are date variables, the dates are 'integer' dates ie don't include a
timestamp. The fact remains that sometimes it works but every now and again
it doesn't. Example below.

Can anyone suggest anything that I should be looking for please.

Regards.
Bill.

dddate CallsRec
28-Jan-04 40
29-Jan-04 27
30-Jan-04 33
02-Feb-04 41
03-Feb-04 0
04-Feb-04 0
05-Feb-04 0
06-Feb-04 0
09-Feb-04 0
10-Feb-04 0
11-Feb-04 0
12-Feb-04 0
13-Feb-04 33
16-Feb-04 37
17-Feb-04 48
18-Feb-04 54

 

[Ken Snell [MVP]]

Can you post the DCount expression and give us some more info about what
you're using it for and in which context (such as VBA code or query or
something else)?

 

[Bill]

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]"," [#RecvdDate] =#"
& [dddate] & "#") AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

dddate is derived from the original callog table and thus will always exist.
I have separated it as when I get it going I will want to do some operations
on it like do calculations based on week commencing instead of days.

I had to do a q#calllog query as the dates in the original table were stored
as strings. #RecvdDate is basically Cdate(RecvdDate) - where RecvdDate is in
the original string format. The function that does this conversion checks
for null or zero string entries and replaces with an extreme date.

If I force 12-Jan-04 into the above SQL then it works, returning the correct
value of 45, though obviously it returns that value for all rows.

Is this enough?

Thanks for the help.

Regards.
Bill

 

[Bill]

Sorry, it was done in a bit of a rush - I have just realised that 12 Jan was
not part of the original example but it is a valid statement. If I had
extended the example then 12 Jan does feature the anomaly of a zero return.

 

[Ken Snell [MVP]]

If ddate is the name of a field in a table, and ddate is a date/time type,
then you don't need the # delimiters in the DCount function:

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]"," [#RecvdDate] ="
& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;


--

Ken Snell
<MS ACCESS MVP>

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]"," [#RecvdDate] =#"
& [dddate] & "#") AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

dddate is derived from the original callog table and thus will always exist.
I have separated it as when I get it going I will want to do some operations
on it like do calculations based on week commencing instead of days.

I had to do a q#calllog query as the dates in the original table were stored
as strings. #RecvdDate is basically Cdate(RecvdDate) - where RecvdDate is in
the original string format. The function that does this conversion checks
for null or zero string entries and replaces with an extreme date.

If I force 12-Jan-04 into the above SQL then it works, returning the correct
value of 45, though obviously it returns that value for all rows.

Is this enough?

Thanks for the help.

Regards.
Bill

Can you post the DCount expression and give us some more info about what
you're using it for and in which context (such as VBA code or query or
something else)?

 

[Bill]

Ken

This is copy/paste of the SQL, which I think is exactly as you have
suggested. This gives me ALL zero's! Am I missing something? I must be,
somewhere, I guess!

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]"," [#RecvdDate] ="
& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

Regards.
Bill.

If ddate is the name of a field in a table, and ddate is a date/time type,
then you don't need the # delimiters in the DCount function:

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]"," [#RecvdDate] ="
& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;


--

Ken Snell
<MS ACCESS MVP>

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]"," [#RecvdDate] =#"
& [dddate] & "#") AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

dddate is derived from the original callog table and thus will always exist.
I have separated it as when I get it going I will want to do some operations
on it like do calculations based on week commencing instead of days.

I had to do a q#calllog query as the dates in the original table were stored
as strings. #RecvdDate is basically Cdate(RecvdDate) - where RecvdDate

is

in
the original string format. The function that does this conversion checks
for null or zero string entries and replaces with an extreme date.

If I force 12-Jan-04 into the above SQL then it works, returning the correct
value of 45, though obviously it returns that value for all rows.

Is this enough?

Thanks for the help.

Regards.
Bill

 

[Ken Snell [MVP]]

Your posted SQL looks ok, although I'd use this:

SELECT q_recdates.dddate, DCount("*","[q#CallLog]"," [#RecvdDate] ="
& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

If you're getting all zeros, I'd look for
- mistyped name for the field #RecvdDate
- #RecvdDate is not a date/time field and is instead a text string
field
- dddate is not a date/time field and is instead a text string
field
- dddate is a date/time field but it contains both date and time
and not just date values
- #RecvdDate is a date/time field but it contains both date and
time and not just date values

If either of the last two are the case, then you'll need to change the
criterion string in the DCount slightly:

SELECT q_recdates.dddate, DCount("*","[q#CallLog]"," [#RecvdDate] Between "
& [dddate] & " And " & [dddate] + 1) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;


--

Ken Snell
<MS ACCESS MVP>

Ken

This is copy/paste of the SQL, which I think is exactly as you have
suggested. This gives me ALL zero's! Am I missing something? I must be,
somewhere, I guess!

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]"," [#RecvdDate] ="
& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

Regards.
Bill.

If ddate is the name of a field in a table, and ddate is a date/time type,
then you don't need the # delimiters in the DCount function:

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]"," [#RecvdDate] ="
& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;


--

Ken Snell
<MS ACCESS MVP>

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]","

[#RecvdDate]
=#"

& [dddate] & "#") AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

dddate is derived from the original callog table and thus will always exist.
I have separated it as when I get it going I will want to do some operations
on it like do calculations based on week commencing instead of days.

I had to do a q#calllog query as the dates in the original table were stored
as strings. #RecvdDate is basically Cdate(RecvdDate) - where RecvdDate

is

in
the original string format. The function that does this conversion checks
for null or zero string entries and replaces with an extreme date.

If I force 12-Jan-04 into the above SQL then it works, returning the correct
value of 45, though obviously it returns that value for all rows.

Is this enough?

Thanks for the help.

Regards.
Bill


Can you post the DCount expression and give us some more info about what
you're using it for and in which context (such as VBA code or query or
something else)?

 

[Ken Snell [MVP]]

Or slightly better:

SELECT q_recdates.dddate, DCount("*","[q#CallLog]"," [#RecvdDate] >= "
& [dddate] & " And [#RecvdDate] < (" & [dddate] & " + 1)") AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

--

Ken Snell
<MS ACCESS MVP>

Your posted SQL looks ok, although I'd use this:

SELECT q_recdates.dddate, DCount("*","[q#CallLog]"," [#RecvdDate] ="
& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

If you're getting all zeros, I'd look for
- mistyped name for the field #RecvdDate
- #RecvdDate is not a date/time field and is instead a text string
field
- dddate is not a date/time field and is instead a text string
field
- dddate is a date/time field but it contains both date and time
and not just date values
- #RecvdDate is a date/time field but it contains both date and
time and not just date values

If either of the last two are the case, then you'll need to change the
criterion string in the DCount slightly:

SELECT q_recdates.dddate, DCount("*","[q#CallLog]"," [#RecvdDate] Between "
& [dddate] & " And " & [dddate] + 1) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;


--

Ken Snell
<MS ACCESS MVP>

Ken

This is copy/paste of the SQL, which I think is exactly as you have
suggested. This gives me ALL zero's! Am I missing something? I must be,
somewhere, I guess!

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]"," [#RecvdDate] ="
& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

Regards.
Bill.

If ddate is the name of a field in a table, and ddate is a date/time type,
then you don't need the # delimiters in the DCount function:

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]","

[#RecvdDate]
="

& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;


--

Ken Snell
<MS ACCESS MVP>

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]"," [#RecvdDate]
=#"
& [dddate] & "#") AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

dddate is derived from the original callog table and thus will always
exist.
I have separated it as when I get it going I will want to do some
operations
on it like do calculations based on week commencing instead of days.

I had to do a q#calllog query as the dates in the original table were
stored
as strings. #RecvdDate is basically Cdate(RecvdDate) - where

RecvdDate
is

in
the original string format. The function that does this conversion checks
for null or zero string entries and replaces with an extreme date.

If I force 12-Jan-04 into the above SQL then it works, returning the
correct
value of 45, though obviously it returns that value for all rows.

Is this enough?

Thanks for the help.

Regards.
Bill


Can you post the DCount expression and give us some more info

about
what

you're using it for and in which context (such as VBA code or

query

or

 

[Bill]

Ken,

This seems to be working!!

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]","[#RecvdDate]=" &
"'" & [dddate] & "'") AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

What is nor clear in the above is the construction of single quotes around
[dddate] in the following.

& "" & [dddate] & "'")

Any idea why this works and why my previous effort only part worked. I
really would like to understand. Forgive me for not trying your latest SQL
posts but as this is working I will now leave well alone. I would like to
understand though!!

Many thanks for your assistance.
Regards.
Bill.

Or slightly better:

SELECT q_recdates.dddate, DCount("*","[q#CallLog]"," [#RecvdDate] >= "
& [dddate] & " And [#RecvdDate] < (" & [dddate] & " + 1)") AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

--

Ken Snell
<MS ACCESS MVP>

Your posted SQL looks ok, although I'd use this:

SELECT q_recdates.dddate, DCount("*","[q#CallLog]"," [#RecvdDate] ="
& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

If you're getting all zeros, I'd look for
- mistyped name for the field #RecvdDate
- #RecvdDate is not a date/time field and is instead a text string
field
- dddate is not a date/time field and is instead a text string
field
- dddate is a date/time field but it contains both date and time
and not just date values
- #RecvdDate is a date/time field but it contains both date and
time and not just date values

If either of the last two are the case, then you'll need to change the
criterion string in the DCount slightly:

SELECT q_recdates.dddate, DCount("*","[q#CallLog]"," [#RecvdDate]

Between

"
& [dddate] & " And " & [dddate] + 1) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;


--

Ken Snell
<MS ACCESS MVP>

Ken

This is copy/paste of the SQL, which I think is exactly as you have
suggested. This gives me ALL zero's! Am I missing something? I must be,
somewhere, I guess!

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]","

[#RecvdDate]
="

& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

Regards.
Bill.


If ddate is the name of a field in a table, and ddate is a date/time type,
then you don't need the # delimiters in the DCount function:

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]"," [#RecvdDate]
="
& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;


--

Ken Snell
<MS ACCESS MVP>

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]"," [#RecvdDate]
=#"
& [dddate] & "#") AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

dddate is derived from the original callog table and thus will always
exist.
I have separated it as when I get it going I will want to do some
operations
on it like do calculations based on week commencing instead of days.

I had to do a q#calllog query as the dates in the original table were
stored
as strings. #RecvdDate is basically Cdate(RecvdDate) - where RecvdDate
is
in
the original string format. The function that does this conversion
checks
for null or zero string entries and replaces with an extreme date.

If I force 12-Jan-04 into the above SQL then it works, returning the
correct
value of 45, though obviously it returns that value for all rows.

Is this enough?

Thanks for the help.

Regards.
Bill


Can you post the DCount expression and give us some more info about
what
you're using it for and in which context (such as VBA code or

query

or
something else)?

 

[Ken Snell [MVP]]

Without seeing your data and table, I can only guess that dddate and
#RecvdDate are not date/time fields, but instead are text fields.

--

Ken Snell
<MS ACCESS MVP>

Ken,

This seems to be working!!

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]","[#RecvdDate]=" &
"'" & [dddate] & "'") AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

What is nor clear in the above is the construction of single quotes around
[dddate] in the following.

& "" & [dddate] & "'")

Any idea why this works and why my previous effort only part worked. I
really would like to understand. Forgive me for not trying your latest SQL
posts but as this is working I will now leave well alone. I would like to
understand though!!

Many thanks for your assistance.
Regards.
Bill.

Or slightly better:

SELECT q_recdates.dddate, DCount("*","[q#CallLog]"," [#RecvdDate] >= "
& [dddate] & " And [#RecvdDate] < (" & [dddate] & " + 1)") AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

--

Ken Snell
<MS ACCESS MVP>

Your posted SQL looks ok, although I'd use this:

SELECT q_recdates.dddate, DCount("*","[q#CallLog]"," [#RecvdDate] ="
& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

If you're getting all zeros, I'd look for
- mistyped name for the field #RecvdDate
- #RecvdDate is not a date/time field and is instead a text string
field
- dddate is not a date/time field and is instead a text string
field
- dddate is a date/time field but it contains both date and time
and not just date values
- #RecvdDate is a date/time field but it contains both date and
time and not just date values

If either of the last two are the case, then you'll need to change the
criterion string in the DCount slightly:

SELECT q_recdates.dddate, DCount("*","[q#CallLog]"," [#RecvdDate]

Between

"
& [dddate] & " And " & [dddate] + 1) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;


--

Ken Snell
<MS ACCESS MVP>




Ken

This is copy/paste of the SQL, which I think is exactly as you have
suggested. This gives me ALL zero's! Am I missing something? I must be,
somewhere, I guess!

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]"," [#RecvdDate]
="
& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

Regards.
Bill.


If ddate is the name of a field in a table, and ddate is a date/time
type,
then you don't need the # delimiters in the DCount function:

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]"," [#RecvdDate]
="
& [dddate]) AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;


--

Ken Snell
<MS ACCESS MVP>

SELECT q_recdates.dddate, DCount("[CallID]","[q#CallLog]","
[#RecvdDate]
=#"
& [dddate] & "#") AS CallsRec
FROM q_recdates
GROUP BY q_recdates.dddate;

dddate is derived from the original callog table and thus will always
exist.
I have separated it as when I get it going I will want to do some
operations
on it like do calculations based on week commencing instead of days.

I had to do a q#calllog query as the dates in the original table were
stored
as strings. #RecvdDate is basically Cdate(RecvdDate) - where RecvdDate
is
in
the original string format. The function that does this conversion
checks
for null or zero string entries and replaces with an extreme date.

If I force 12-Jan-04 into the above SQL then it works, returning the
correct
value of 45, though obviously it returns that value for all rows.

Is this enough?

Thanks for the help.

Regards.
Bill


Can you post the DCount expression and give us some more info about
what
you're using it for and in which context (such as VBA code or query
or
something else)?

 

[Bill]

dddate VarType
05-Jan-04 7
06-Jan-04 7
07-Jan-04 7
08-Jan-04 7
09-Jan-04 7
12-Jan-04 7
13-Jan-04 7
14-Jan-04 7
15-Jan-04 7
16-Jan-04 7
19-Jan-04 7
20-Jan-04 7
21-Jan-04 7



A bit of a mystery then. Anyway its working now!
Thanks for your help.
Bill.

 

[Ken Snell [MVP]]

It would seem so....to be a mystery, that is.