Odd DCount problem

[TonyB]

I'm using a Dcount call in one of my vba modules which is behaving
strangely.
I have a table tblSupport with a field [CallDate]. These dates range over
the last few months. On some dates there is more than 1 record, and dcount
is supposed to return the number of records for that date. [CallDate] is set
to a default value of Date() when a new record is created, and so should
reflect the date when the record was created.

I'm calling DCount with following syntax (note using UK date formats for
region settings)
dcount("[Date of Call]","tblSupport","[CallDate]=#10/03/2005#")

I can type this in the immediate window using different values for the date,
and for some records it is correct, returning 1,2,3 etc, but for other
records that have a matching date in [CallDate] dcount returns 0 ? So
19/02/2005 works correctly, but 10/02/2005 doesn't. But there are records
with both dates in the table.
Does anyone have any ideas what is happening here ?
Thanks
Tony

 

[fredg]

I'm using a Dcount call in one of my vba modules which is behaving
strangely.
I have a table tblSupport with a field [CallDate]. These dates range over
the last few months. On some dates there is more than 1 record, and dcount
is supposed to return the number of records for that date. [CallDate] is set
to a default value of Date() when a new record is created, and so should
reflect the date when the record was created.

I'm calling DCount with following syntax (note using UK date formats for
region settings)
dcount("[Date of Call]","tblSupport","[CallDate]=#10/03/2005#")

I can type this in the immediate window using different values for the date,
and for some records it is correct, returning 1,2,3 etc, but for other
records that have a matching date in [CallDate] dcount returns 0 ? So
19/02/2005 works correctly, but 10/02/2005 doesn't. But there are records
with both dates in the table.
Does anyone have any ideas what is happening here ?
Thanks
Tony

I'm not sure whether the UK formatting is creating your problem, so
try it both ways like this (for the month of March):

=dcount("*","tblSupport","[CallDate]=#10/03/2005#")

or..
=dcount("*","tblSupport","[CallDate]=#3/10/2005#")

Does either give a better result?

 

[JaRa]

This always the problem Dates Dates and Dates

I would Solve this the following way

=dcount("*","tblSupport","((Year([CallDate])*100+Month([CallDate]))*100+Day([CallDate])=((Year(#10/03/2005#)*100+Month(#10/03/2005#))*100+Day(#10/03/2005#)")

I but more coding but this should work always

- Raoul

I'm using a Dcount call in one of my vba modules which is behaving
strangely.
I have a table tblSupport with a field [CallDate]. These dates range over
the last few months. On some dates there is more than 1 record, and dcount
is supposed to return the number of records for that date. [CallDate] is set
to a default value of Date() when a new record is created, and so should
reflect the date when the record was created.

I'm calling DCount with following syntax (note using UK date formats for
region settings)
dcount("[Date of Call]","tblSupport","[CallDate]=#10/03/2005#")

I can type this in the immediate window using different values for the date,
and for some records it is correct, returning 1,2,3 etc, but for other
records that have a matching date in [CallDate] dcount returns 0 ? So
19/02/2005 works correctly, but 10/02/2005 doesn't. But there are records
with both dates in the table.
Does anyone have any ideas what is happening here ?
Thanks
Tony

I'm not sure whether the UK formatting is creating your problem, so
try it both ways like this (for the month of March):

=dcount("*","tblSupport","[CallDate]=#10/03/2005#")

or..
=dcount("*","tblSupport","[CallDate]=#3/10/2005#")

Does either give a better result?

 

[TonyB]

Hi Fred,
As you suggested, #03/10/2005# works correctly so here in this case a usa
format date works.
But #19/02/2005# works correctly, which cannot be a usa date as it would be
2nd of 19th month ?
I cannot see how 19/02/2005 works but you have to use 03/10/2005 instead of
10/03/2005 ? Dates baffle me in Access !

Is this going wrong because [CallDate] will be stored in usa format in the
table, but is displayed in uk format ? And it works ok for 19/02/2005
because access works out that 19/02/2005 must be not in usa format but uk
format and returns the correct number, but 10/03/2005 is treated as usa
format ?

Does it explain better that in my code I actually use a piece of code to
create the 3rd parameter to dcount as in
[CallDate] & "=#" & Format(Date, "dd/mm/yyyy") & "#" ?

Thanks
Tony

I'm using a Dcount call in one of my vba modules which is behaving
strangely.
I have a table tblSupport with a field [CallDate]. These dates range over
the last few months. On some dates there is more than 1 record, and dcount
is supposed to return the number of records for that date. [CallDate] is set
to a default value of Date() when a new record is created, and so should
reflect the date when the record was created.

I'm calling DCount with following syntax (note using UK date formats for
region settings)
dcount("[Date of Call]","tblSupport","[CallDate]=#10/03/2005#")

I can type this in the immediate window using different values for the date,
and for some records it is correct, returning 1,2,3 etc, but for other
records that have a matching date in [CallDate] dcount returns 0 ? So
19/02/2005 works correctly, but 10/02/2005 doesn't. But there are records
with both dates in the table.
Does anyone have any ideas what is happening here ?
Thanks
Tony

I'm not sure whether the UK formatting is creating your problem, so
try it both ways like this (for the month of March):

=dcount("*","tblSupport","[CallDate]=#10/03/2005#")

or..
=dcount("*","tblSupport","[CallDate]=#3/10/2005#")

Does either give a better result?

 

[TonyB]

Hi JaRa,
Thanks for the info. I'll try it out later but I'm puzzled as to what you
are doing here.
You multiply each of day/month/year by 100 for both the field value and the
item it is compared to ?
Is it basically extracting the d/m/y for each item, adding the d+m+y
together and comparing the two values so that the order of dmy doesn't
matter ?
Regards
Tony

 

[JaRa]

Exactly i make it position independent. There are other possibilities but his
I like best.

-Raoul

Hi JaRa,
Thanks for the info. I'll try it out later but I'm puzzled as to what you
are doing here.
You multiply each of day/month/year by 100 for both the field value and the
item it is compared to ?
Is it basically extracting the d/m/y for each item, adding the d+m+y
together and comparing the two values so that the order of dmy doesn't
matter ?
Regards
Tony

This always the problem Dates Dates and Dates

I would Solve this the following way

=dcount("*","tblSupport","((Year([CallDate])*100+Month([CallDate]))*100+Day(
[CallDate])=((Year(#10/03/2005#)*100+Month(#10/03/2005#))*100+Day(#10/03/200
5#)")

I but more coding but this should work always

- Raoul

:

 

[John Spencer (MVP)]

19/02/2005 works because Access figures out that 19 can't be the month, but 02
can be. So it switches them to 02/19. I can't say that I like this "feature"
but it is there and I can't do much about it except be aware of it.

One thing that seems to work consistently is to use the format yyyy-mm-dd in the
query.

Hi Fred,
As you suggested, #03/10/2005# works correctly so here in this case a usa
format date works.
But #19/02/2005# works correctly, which cannot be a usa date as it would be
2nd of 19th month ?
I cannot see how 19/02/2005 works but you have to use 03/10/2005 instead of
10/03/2005 ? Dates baffle me in Access !

Is this going wrong because [CallDate] will be stored in usa format in the
table, but is displayed in uk format ? And it works ok for 19/02/2005
because access works out that 19/02/2005 must be not in usa format but uk
format and returns the correct number, but 10/03/2005 is treated as usa
format ?

Does it explain better that in my code I actually use a piece of code to
create the 3rd parameter to dcount as in
[CallDate] & "=#" & Format(Date, "dd/mm/yyyy") & "#" ?

Thanks
Tony

I'm using a Dcount call in one of my vba modules which is behaving
strangely.
I have a table tblSupport with a field [CallDate]. These dates range over
the last few months. On some dates there is more than 1 record, and dcount
is supposed to return the number of records for that date. [CallDate] is set
to a default value of Date() when a new record is created, and so should
reflect the date when the record was created.

I'm calling DCount with following syntax (note using UK date formats for
region settings)
dcount("[Date of Call]","tblSupport","[CallDate]=#10/03/2005#")

I can type this in the immediate window using different values for the date,
and for some records it is correct, returning 1,2,3 etc, but for other
records that have a matching date in [CallDate] dcount returns 0 ? So
19/02/2005 works correctly, but 10/02/2005 doesn't. But there are records
with both dates in the table.
Does anyone have any ideas what is happening here ?
Thanks
Tony

I'm not sure whether the UK formatting is creating your problem, so
try it both ways like this (for the month of March):

=dcount("*","tblSupport","[CallDate]=#10/03/2005#")

or..
=dcount("*","tblSupport","[CallDate]=#3/10/2005#")

Does either give a better result?