Count the number of times the date is a Monday, Tuesday, etc

[Basenji]

Using Access 2003 in the report footer I want to the count the number of
times that a consultation occurs on a Monday, Tuesday, etc. In the detailed
section the date field is [ConsultDate] with a dddd, mmmm d yyyy format so
that the date shows as Wednesday, July 29, 2009. Assistance is needed to come
up with an expression for unbound control(s) in the report footer so that
between a start and end date (using a parameter) it would show that for a
total of 50 consultations, 5 were done on Mondays, 10 on Tuesdays, 15 were
done on Wednesday, 12 on Thursday and 8 on Friday, 0 on Saturday and 0 on
Sunday.

Thank you.

 

[Duane Hookom]

Try:
for Monday
=Sum(Abs(Weekday(ConsultDate=2)))
for Tuesday
=Sum(Abs(Weekday(ConsultDate=3)))
etc

 

[Basenji]

I am not doing something right as the total for Monday comes up to 378. The
total number of records is 54; the total for Monday should be 8. This is the
expression: =Sum(Abs(Weekday([ConsultDate]=2)))

Try:
for Monday
=Sum(Abs(Weekday(ConsultDate=2)))
for Tuesday
=Sum(Abs(Weekday(ConsultDate=3)))
etc

--
Duane Hookom
Microsoft Access MVP

Using Access 2003 in the report footer I want to the count the number of
times that a consultation occurs on a Monday, Tuesday, etc. In the detailed
section the date field is [ConsultDate] with a dddd, mmmm d yyyy format so
that the date shows as Wednesday, July 29, 2009. Assistance is needed to come
up with an expression for unbound control(s) in the report footer so that
between a start and end date (using a parameter) it would show that for a
total of 50 consultations, 5 were done on Mondays, 10 on Tuesdays, 15 were
done on Wednesday, 12 on Thursday and 8 on Friday, 0 on Saturday and 0 on
Sunday.

Thank you.

 

[Duane Hookom]

Try fix my typos ;-)

for Monday
=Sum( Abs( Weekday(ConsultDate)=2 ) )

--
Duane Hookom
Microsoft Access MVP

I am not doing something right as the total for Monday comes up to 378. The
total number of records is 54; the total for Monday should be 8. This is the
expression: =Sum(Abs(Weekday([ConsultDate]=2)))

Try:
for Monday
=Sum(Abs(Weekday(ConsultDate=2)))
for Tuesday
=Sum(Abs(Weekday(ConsultDate=3)))
etc

--
Duane Hookom
Microsoft Access MVP

Using Access 2003 in the report footer I want to the count the number of
times that a consultation occurs on a Monday, Tuesday, etc. In the detailed
section the date field is [ConsultDate] with a dddd, mmmm d yyyy format so
that the date shows as Wednesday, July 29, 2009. Assistance is needed to come
up with an expression for unbound control(s) in the report footer so that
between a start and end date (using a parameter) it would show that for a
total of 50 consultations, 5 were done on Mondays, 10 on Tuesdays, 15 were
done on Wednesday, 12 on Thursday and 8 on Friday, 0 on Saturday and 0 on
Sunday.

Thank you.

 

[Basenji]

The totals were right on. Many thanks for your assistance. Now a question:
what is the rational for using the sum/abs function for counting rather than
using count or dcount? I have noticed in some other questions about counting
that the feedback is the use of the sum function when doing counts.

Try fix my typos ;-)

for Monday
=Sum( Abs( Weekday(ConsultDate)=2 ) )

--
Duane Hookom
Microsoft Access MVP

I am not doing something right as the total for Monday comes up to 378. The
total number of records is 54; the total for Monday should be 8. This is the
expression: =Sum(Abs(Weekday([ConsultDate]=2)))

Try:
for Monday
=Sum(Abs(Weekday(ConsultDate=2)))
for Tuesday
=Sum(Abs(Weekday(ConsultDate=3)))
etc

--
Duane Hookom
Microsoft Access MVP


:

Using Access 2003 in the report footer I want to the count the number of
times that a consultation occurs on a Monday, Tuesday, etc. In the detailed
section the date field is [ConsultDate] with a dddd, mmmm d yyyy format so
that the date shows as Wednesday, July 29, 2009. Assistance is needed to come
up with an expression for unbound control(s) in the report footer so that
between a start and end date (using a parameter) it would show that for a
total of 50 consultations, 5 were done on Mondays, 10 on Tuesdays, 15 were
done on Wednesday, 12 on Thursday and 8 on Friday, 0 on Saturday and 0 on
Sunday.

Thank you.

 

[Duane Hookom]

This should be much more efficient than using DCount().

This expression results in either True/-1 or False/0
Weekday(ConsultDate)=2

The Abs() function converts the -1 to +1.

The Sum() basically Sums the Abs() results which is the same as counting the
trues.

--
Duane Hookom
Microsoft Access MVP

The totals were right on. Many thanks for your assistance. Now a question:
what is the rational for using the sum/abs function for counting rather than
using count or dcount? I have noticed in some other questions about counting
that the feedback is the use of the sum function when doing counts.

Try fix my typos ;-)

for Monday
=Sum( Abs( Weekday(ConsultDate)=2 ) )

--
Duane Hookom
Microsoft Access MVP

I am not doing something right as the total for Monday comes up to 378. The
total number of records is 54; the total for Monday should be 8. This is the
expression: =Sum(Abs(Weekday([ConsultDate]=2)))

:

Try:
for Monday
=Sum(Abs(Weekday(ConsultDate=2)))
for Tuesday
=Sum(Abs(Weekday(ConsultDate=3)))
etc

--
Duane Hookom
Microsoft Access MVP


:

Using Access 2003 in the report footer I want to the count the number of
times that a consultation occurs on a Monday, Tuesday, etc. In the detailed
section the date field is [ConsultDate] with a dddd, mmmm d yyyy format so
that the date shows as Wednesday, July 29, 2009. Assistance is needed to come
up with an expression for unbound control(s) in the report footer so that
between a start and end date (using a parameter) it would show that for a
total of 50 consultations, 5 were done on Mondays, 10 on Tuesdays, 15 were
done on Wednesday, 12 on Thursday and 8 on Friday, 0 on Saturday and 0 on
Sunday.

Thank you.