How do use the Dcount?

[Guest]

I am trying to count requests for each employee that have been completed in a
a given day e.g., on

07/15 I rcvd 3 requests, 2 were completed on 7/15 and 1 is still pending but
is completed 7/16
07/16 I rcvd 4 requests, 1 was completed on 7/16 and 4 are pending

So my report kinda looks like this

Date rcvd Completed pending
07/15 3 2 1
07/16 4 1 4

My current Dcount statement is as follows
=DCount("[RqstCompleted]","qryRqst","[EmpID]= " & [EmpID] & "and [Date]= " &
[Date] & "and[DateCompleted]=[date]" & "and[RqstCompleted]=true")

I think am doing something wrong

 

[Wayne Morgan]

I see two things right off-hand. The first is that you're using reserved
words, such as Date, for field and control names. Since you have bracketed
them, this probably isn't causing you a problem. The other thing I see is
that you are missing spaces on each side of the word "and" in several
places.

Looking further, you have a RqstCompleted field and a DateCompleted field.
If there is a date in DateCompleted wouldn't RqstCompleted always be True?
If so, do you need both fields? Also, in the WHERE part of the statement,
you have all of [DateCompleted]=[Date] inside the quotes. Is [Date] supposed
to be supplied by the form? If so, it needs to be outside the quotes as it
is for the [Date]=" & [Date] & "... portion. Also, if you include both
[Date] and [DateCompleted] in the criteria where both are equal to [Date] on
the form then you will be retrieving request that were entered and completed
on the same day, not requests that were completed on the date in the form,
regardless of when the request was actually made.

 

[Guest]

Thanks for your response,

RqstCompleted is a checkbox that one clicks and puts the date ie the
[DateCompleted], so if the rqstCompleted is checked then we do have a the
DateCompleted.
Date is supplied by default todays date that the request was entered in the
Database.

So how do I then do the Dcount.

Please help!

I see two things right off-hand. The first is that you're using reserved
words, such as Date, for field and control names. Since you have bracketed
them, this probably isn't causing you a problem. The other thing I see is
that you are missing spaces on each side of the word "and" in several
places.

Looking further, you have a RqstCompleted field and a DateCompleted field.
If there is a date in DateCompleted wouldn't RqstCompleted always be True?
If so, do you need both fields? Also, in the WHERE part of the statement,
you have all of [DateCompleted]=[Date] inside the quotes. Is [Date] supposed
to be supplied by the form? If so, it needs to be outside the quotes as it
is for the [Date]=" & [Date] & "... portion. Also, if you include both
[Date] and [DateCompleted] in the criteria where both are equal to [Date] on
the form then you will be retrieving request that were entered and completed
on the same day, not requests that were completed on the date in the form,
regardless of when the request was actually made.

--
Wayne Morgan
MS Access MVP

I am trying to count requests for each employee that have been completed in
a
a given day e.g., on

07/15 I rcvd 3 requests, 2 were completed on 7/15 and 1 is still pending
but
is completed 7/16
07/16 I rcvd 4 requests, 1 was completed on 7/16 and 4 are pending

So my report kinda looks like this

Date rcvd Completed pending
07/15 3 2 1
07/16 4 1 4

My current Dcount statement is as follows
=DCount("[RqstCompleted]","qryRqst","[EmpID]= " & [EmpID] & "and [Date]= "
&
[Date] & "and[DateCompleted]=[date]" & "and[RqstCompleted]=true")

I think am doing something wrong

 

[Wayne Morgan]

I understand that checking the checkbox adds the date to DateCompleted.
However, it is not necessary to store the value of this checkbox in the
table. In the form's Current event you would run code similar to this:

Me.rqstCompleted = IsDate([DateCompleted])

Therefore, if there is a date in DateCompleted, the check box will be check
and if there isn't then it won't be. The user could still check it to add
the date when needed. The above line works because IsDate() will return True
or False.

Now, to amend your DCount statement:

=DCount("*","qryRqst","[EmpID]= " & [EmpID] & " and [DateCompleted]=#" &
[Date] & "#")

The newsreader has probably wrapped the line. It should all be on one line.
Also, the # signs are date delimiters, just as you use " to delimit strings.
Again, if the DateCompleted matches the date you have entered then you can
assume that RqstCompleted is True, so it doesn't need to be in the criteria.
I removed the first Date because, as mentioned in the previous message, with
that there you won't get all records that were completed on Date, instead
you will get all records that were started AND completed on the day they
were started, [Date]. With a DCount() statement, you usually don't need to
count a particular field, you are counting the number of records, not the
value in the field. The limiting element on what is counted is the criteria
part of the statement. So I replaced the name of the field with *. The
syntax for EmpID assumes this to be a number data type field, is this
correct?

The entry in [Date] will need to be in US date format. If not, then it will
need to be formatted:

=DCount("*","qryRqst","[EmpID]= " & [EmpID] & " and [DateCompleted]=#" &
Format([Date], "mm/dd/yyyy") & "#")

 

[Guest]

Thanks for your reply,

I did put the dcount as you had put but when I look at the report, its only
counting the requests that have datecompleted = date.

this is also how my report is based on....... I did a qry of the table and
the report is based on that qry but its grouped by Date
So I noticed that if employee X did not receive any rqsts let say 07/15 but
completed outstanding rqsts like lets say for 07/14, this employee will not
appear in the list

I grouped it by date so that I can be able to get how many rqsts came in on
that day for @ employee.

I understand that checking the checkbox adds the date to DateCompleted.
However, it is not necessary to store the value of this checkbox in the
table. In the form's Current event you would run code similar to this:

Me.rqstCompleted = IsDate([DateCompleted])

Therefore, if there is a date in DateCompleted, the check box will be check
and if there isn't then it won't be. The user could still check it to add
the date when needed. The above line works because IsDate() will return True
or False.

Now, to amend your DCount statement:

=DCount("*","qryRqst","[EmpID]= " & [EmpID] & " and [DateCompleted]=#" &
[Date] & "#")

The newsreader has probably wrapped the line. It should all be on one line.
Also, the # signs are date delimiters, just as you use " to delimit strings.
Again, if the DateCompleted matches the date you have entered then you can
assume that RqstCompleted is True, so it doesn't need to be in the criteria.
I removed the first Date because, as mentioned in the previous message, with
that there you won't get all records that were completed on Date, instead
you will get all records that were started AND completed on the day they
were started, [Date]. With a DCount() statement, you usually don't need to
count a particular field, you are counting the number of records, not the
value in the field. The limiting element on what is counted is the criteria
part of the statement. So I replaced the name of the field with *. The
syntax for EmpID assumes this to be a number data type field, is this
correct?

The entry in [Date] will need to be in US date format. If not, then it will
need to be formatted:

=DCount("*","qryRqst","[EmpID]= " & [EmpID] & " and [DateCompleted]=#" &
Format([Date], "mm/dd/yyyy") & "#")

--
Wayne Morgan
MS Access MVP

Thanks for your response,

RqstCompleted is a checkbox that one clicks and puts the date ie the
[DateCompleted], so if the rqstCompleted is checked then we do have a the
DateCompleted.
Date is supplied by default todays date that the request was entered in
the
Database.

So how do I then do the Dcount.

Please help!

 

[Wayne Morgan]

You are correct about what it is counting. I thought your first message said
that you wanted it counted that way. Please let me know again what exactly
you're wanting to count and I'll see what I can do.

 

[Guest]

You are right, in my first statement, it was counting only the dates that =
the rqstcompleted but that is not right, what I want it to count is the rqsts
that are finished on a given regrles of when the rqst came in, e.g For
Employee X below

7/15 3 rqsts came in, Employee X completed 2, and 1 would need a followup
7/16 5 rqsts came in, Employee X completed 0, and would have 5 for followup
7/17 no rqst came in, but completed 8 previous rqst that needed followup

so my report looks like this
Date Incoming rqst completed rqsts followup rqsts
07/15 3 2 1
07/16 5 0 5
07/17 0 8 0

I hope this helps in better understanding the problem.

Thanks in advance!

 

[Wayne Morgan]

It should be counting requests that were finished on the date supplied,
regardless of when the request came in. The date supplied is the date in
[Date]. Is this a date field on the report and if so, what date does it
contain (i.e. what is the significance of this date)? Where are you entering
the date you want to see the count for? Since this is a reserved word, could
it be getting mixed up with the Date() function? If that is the case, try
changing the name of the textbox that the Date field is bound to to txtDate
and use that name [txtDate] in the DCount instead of [Date]. Also, the date
column you are showing in your example doesn't have a year with it. Is it
stored this way or is that just how it is formatted?

 

[Guest]

It should be counting requests that were finished on the date supplied,
regardless of when the request came in. The date supplied is the date in
[Date]. Is this a date field on the report and if so, what date does it
contain (i.e. what is the significance of this date)?

****
[Date] is a filed in the table rqsts its also a textbox in the report
In my rqst form, the date is automatically populated by todays date ie the
date when an employee receives a rqst and also count how many rqsts they
completed on that date regadles of when it received.
*****

Where are you entering

the date you want to see the count for?

****
The date am entering is in the form, its set to date()
So am trying to do get a lot of information on the same line I can say ie
count how many rqsts came in that day and
****

Since this is a reserved word, could

it be getting mixed up with the Date() function? If that is the case, try
changing the name of the textbox that the Date field is bound to to txtDate
and use that name [txtDate] in the DCount instead of [Date]. Also, the date
column you are showing in your example doesn't have a year with it. Is it
stored this way or is that just how it is formatted?

*****
Well actually I just shortened it it should show 7/15/2005 and that is how
its stored
*****

 

[Wayne Morgan]

By your description, it appears you are using the same field for storing the
date when a request arrives and when the request is completed. These would
need to be two different date fields or one will over write the other.

--
Wayne Morgan
MS Access MVP

It should be counting requests that were finished on the date supplied,
regardless of when the request came in. The date supplied is the date in
[Date]. Is this a date field on the report and if so, what date does it
contain (i.e. what is the significance of this date)?

****
[Date] is a filed in the table rqsts its also a textbox in the report
In my rqst form, the date is automatically populated by todays date ie the
date when an employee receives a rqst and also count how many rqsts they
completed on that date regadles of when it received.
*****

Where are you entering

the date you want to see the count for?

****
The date am entering is in the form, its set to date()
So am trying to do get a lot of information on the same line I can say ie
count how many rqsts came in that day and
****

Since this is a reserved word, could

it be getting mixed up with the Date() function? If that is the case, try
changing the name of the textbox that the Date field is bound to to
txtDate
and use that name [txtDate] in the DCount instead of [Date]. Also, the
date
column you are showing in your example doesn't have a year with it. Is it
stored this way or is that just how it is formatted?

*****
Well actually I just shortened it it should show 7/15/2005 and that is how
its stored
*****

 

[Guest]

I have [Date] when the request comes in and [DateCompleted] when the rqst was
marked for complete.

By your description, it appears you are using the same field for storing the
date when a request arrives and when the request is completed. These would
need to be two different date fields or one will over write the other.

--
Wayne Morgan
MS Access MVP

It should be counting requests that were finished on the date supplied,
regardless of when the request came in. The date supplied is the date in
[Date]. Is this a date field on the report and if so, what date does it
contain (i.e. what is the significance of this date)?

****
[Date] is a filed in the table rqsts its also a textbox in the report
In my rqst form, the date is automatically populated by todays date ie the
date when an employee receives a rqst and also count how many rqsts they
completed on that date regadles of when it received.
*****

Where are you entering

the date you want to see the count for?

****
The date am entering is in the form, its set to date()
So am trying to do get a lot of information on the same line I can say ie
count how many rqsts came in that day and
****

Since this is a reserved word, could

it be getting mixed up with the Date() function? If that is the case, try
changing the name of the textbox that the Date field is bound to to
txtDate
and use that name [txtDate] in the DCount instead of [Date]. Also, the
date
column you are showing in your example doesn't have a year with it. Is it
stored this way or is that just how it is formatted?

*****
Well actually I just shortened it it should show 7/15/2005 and that is how
its stored
*****

 

[Wayne Morgan]

Ok, going back to the equation:

=DCount("*","qryRqst","[EmpID]= " & [EmpID] & " and [DateCompleted]=#" &
[Date] & "#")

Where it has [Date], you need to put in whatever you are using to indicate
what date you are wanting to get a count for. If that record has a
DateCompleted value and you put that in for Date, then you will get a count
of the records completed on that DateCompleted, including the current
record.

 

[Guest]

Fianly, its working but not 100%, the part that is not working is as follows,
my report is grouped by [date] ie the date when rqsts were received. so if an
employee did not receive a rqst on lets say 07/15/2005, but completed
outstanding rqsts from a different date, his name will not appear on the
report, how do I take care of this?

Thanks for you help, you have been very helpful!

Ok, going back to the equation:

=DCount("*","qryRqst","[EmpID]= " & [EmpID] & " and [DateCompleted]=#" &
[Date] & "#")

Where it has [Date], you need to put in whatever you are using to indicate
what date you are wanting to get a count for. If that record has a
DateCompleted value and you put that in for Date, then you will get a count
of the records completed on that DateCompleted, including the current
record.

--
Wayne Morgan
MS Access MVP

I have [Date] when the request comes in and [DateCompleted] when the rqst
was
marked for complete.

 

[Wayne Morgan]

You have to ask what the premise of your report is. Simply grouping by the
date the request was received wouldn't eliminate it from the report, it
would just show up under the date the request was made, not the date it was
completed. However, if you're limiting (filtering) the dates you are seeing
by the date the request was received and that date is outside the range
you've given, then yes, it won't show. If you are filtering the dates, where
are you doing it? Is it being done with a filter in the report or is it
being done in the query feeding the report? Please post the filter or query
(SQL view).