DCount in a subform

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

I am attempting to do a record count on a column in a
subform. When I use the DCount, I retrieve the total
number of records from the table the subform is based on.
If I use the Count function, I get only the number of
records present in the subform column. Unfortunatly I
cannot use the Count function because I want to exclude
certain values from the count. Here is how my form is set
up:
Main form is FA Schedule, contains employee name and ID
number from the employee table. Subform is Schedule
subform, contains employee schedule information from the
Schedule table. The fields in this table are Time,
Sunday....Saturday.When I use =DCount
("[Monday]","[Schedule]","[Monday] Between 1 and 700") in
a textbox located in the subform form footer I return 21
records for Monday, which is true if I want the number of
records for all employees. But in this case I only want
the number of records for the current employee displayed,
so the number should actually be 11.Any suggestions on
how I can remedy this problem will be appreciated.
Although I am novice at this, I am not adverse to using
VB or VBA to accomplish my goal.
To verify that I have the correct name of the subform, I
recreated the subform and checked the name.
These are the variations of DCount I have used to try and
puul the information for the current employee from the
table.
=DCount("[Monday]","[Schedule]","[Monday] Between 1 and
700 AND [FADevRyID] = " & [Forms]![Schedule subform]![
FADevRyID]) Returns #Name?
=DCount("[Monday]","Schedule","[Monday]") Returns all
records for Monday from table
=DCount("[Monday]","Schedule","[Monday] Between 1 and
750") Returns all records for Monday from table
=DCount("[Monday]","Schedule","[Monday]=" & Form.[Faculty
Assistant Schedule]) Returns #Name?
=DCount("Monday","[Schedule]","[Monday]= '" & Me.
[Schedule subform] & "'") Returns #Name?
=DCount("[Monday]","Schedule","[Monday] =" &
[Forms].Schedule) Returns #Name?
=DCount("[Monday]","Schedule","[Monday]=" & Form.
[Schedule subform]) Returns #Name?
 
Try this:

=DCount("*","[Schedule]","[Monday] Between 1 and
700 AND [FADevRyID] = " & Me.FADevRyID)
 
Ken,

Thank you for you help. Unfortunately I am still getting
the #Name? error. I am not understanding why it cannot
find the subform. The name is correct; I have verified it
several times both by recreating the subform and by
checking the name in the properties sheet.

Roger
-----Original Message-----
Try this:

=DCount("*","[Schedule]","[Monday] Between 1 and
700 AND [FADevRyID] = " & Me.FADevRyID)


--
Ken Snell
<MS ACCESS MVP>

Roger said:
I am attempting to do a record count on a column in a
subform. When I use the DCount, I retrieve the total
number of records from the table the subform is based on.
If I use the Count function, I get only the number of
records present in the subform column. Unfortunatly I
cannot use the Count function because I want to exclude
certain values from the count. Here is how my form is set
up:
Main form is FA Schedule, contains employee name and ID
number from the employee table. Subform is Schedule
subform, contains employee schedule information from the
Schedule table. The fields in this table are Time,
Sunday....Saturday.When I use =DCount
("[Monday]","[Schedule]","[Monday] Between 1 and 700") in
a textbox located in the subform form footer I return 21
records for Monday, which is true if I want the number of
records for all employees. But in this case I only want
the number of records for the current employee displayed,
so the number should actually be 11.Any suggestions on
how I can remedy this problem will be appreciated.
Although I am novice at this, I am not adverse to using
VB or VBA to accomplish my goal.
To verify that I have the correct name of the subform, I
recreated the subform and checked the name.
These are the variations of DCount I have used to try and
puul the information for the current employee from the
table.
=DCount("[Monday]","[Schedule]","[Monday] Between 1 and
700 AND [FADevRyID] = " & [Forms]![Schedule subform]![
FADevRyID]) Returns #Name?
=DCount("[Monday]","Schedule","[Monday]") Returns all
records for Monday from table
=DCount("[Monday]","Schedule","[Monday] Between 1 and
750") Returns all records for Monday from table
=DCount("[Monday]","Schedule","[Monday]=" & Form. [Faculty
Assistant Schedule]) Returns #Name?
=DCount("Monday","[Schedule]","[Monday]= '" & Me.
[Schedule subform] & "'") Returns #Name?
=DCount("[Monday]","Schedule","[Monday] =" &
[Forms].Schedule) Returns #Name?
=DCount("[Monday]","Schedule","[Monday]=" & Form.
[Schedule subform]) Returns #Name?
Click to expand...


.
Click to expand...
 
OK -

Let's back up a bit here.

DCount is used to get a count of the number of records in a table or query,
based on a "WHERE" criterion expression that you provide. It will not work
on a subform directly; however, you can use the subform's record source
string as the name of the table or query in the DCount.

If you want to get to the number of records that are displayed on the
subform, let's use the subform's recordsetclone's recordcount property:

NumberOfRecords = Me.SubformName.Form.RecordsetClone.RecordCount

Note that "SubformName" is the name of the subform control (the control on
the main form that holds the subform; this may or may not be the name of the
form that is serving as the subform). The above expression assumes that the
code is running in the main form.

--
Ken Snell
<MS ACCESS MVP>

ROger said:
Ken,

Thank you for you help. Unfortunately I am still getting
the #Name? error. I am not understanding why it cannot
find the subform. The name is correct; I have verified it
several times both by recreating the subform and by
checking the name in the properties sheet.

Roger
-----Original Message-----
Try this:

=DCount("*","[Schedule]","[Monday] Between 1 and
700 AND [FADevRyID] = " & Me.FADevRyID)


--
Ken Snell
<MS ACCESS MVP>

Roger said:
I am attempting to do a record count on a column in a
subform. When I use the DCount, I retrieve the total
number of records from the table the subform is based on.
If I use the Count function, I get only the number of
records present in the subform column. Unfortunatly I
cannot use the Count function because I want to exclude
certain values from the count. Here is how my form is set
up:
Main form is FA Schedule, contains employee name and ID
number from the employee table. Subform is Schedule
subform, contains employee schedule information from the
Schedule table. The fields in this table are Time,
Sunday....Saturday.When I use =DCount
("[Monday]","[Schedule]","[Monday] Between 1 and 700") in
a textbox located in the subform form footer I return 21
records for Monday, which is true if I want the number of
records for all employees. But in this case I only want
the number of records for the current employee displayed,
so the number should actually be 11.Any suggestions on
how I can remedy this problem will be appreciated.
Although I am novice at this, I am not adverse to using
VB or VBA to accomplish my goal.
To verify that I have the correct name of the subform, I
recreated the subform and checked the name.
These are the variations of DCount I have used to try and
puul the information for the current employee from the
table.
=DCount("[Monday]","[Schedule]","[Monday] Between 1 and
700 AND [FADevRyID] = " & [Forms]![Schedule subform]![
FADevRyID]) Returns #Name?
=DCount("[Monday]","Schedule","[Monday]") Returns all
records for Monday from table
=DCount("[Monday]","Schedule","[Monday] Between 1 and
750") Returns all records for Monday from table
=DCount("[Monday]","Schedule","[Monday]=" & Form. [Faculty
Assistant Schedule]) Returns #Name?
=DCount("Monday","[Schedule]","[Monday]= '" & Me.
[Schedule subform] & "'") Returns #Name?
=DCount("[Monday]","Schedule","[Monday] =" &
[Forms].Schedule) Returns #Name?
=DCount("[Monday]","Schedule","[Monday]=" & Form.
[Schedule subform]) Returns #Name?
Click to expand...


.
Click to expand...
Click to expand...
 
Thank you Ken. That has me going in the right direction.
I now have the record count for the employee displayed.
Is there a way to get a count on just one column? What I
need to do is ignore the time column and add just those
fields that have data in them that meet a certain
criteria. For example, if Monday is from 7 AM to 5 PM and
has 3 classes scheduled for a total of 3 hours, 2 hours
are marked as Available, and 4 hours marked as Class, I
should only return 3 as my count. I want to ignore the
time column completely. There are also certain data I
want to ignore such as Class, and Available. This is why
I thought I could use the DCount function. I have various
criteria I want the count to be based on.

Thanks
-----Original Message-----
OK -

Let's back up a bit here.

DCount is used to get a count of the number of records in a table or query,
based on a "WHERE" criterion expression that you provide. It will not work
on a subform directly; however, you can use the subform's record source
string as the name of the table or query in the DCount.

If you want to get to the number of records that are displayed on the
subform, let's use the subform's recordsetclone's recordcount property:

NumberOfRecords = Me.SubformName.Form.RecordsetClone.RecordCount

Note that "SubformName" is the name of the subform control (the control on
the main form that holds the subform; this may or may not be the name of the
form that is serving as the subform). The above expression assumes that the
code is running in the main form.

--
Ken Snell
<MS ACCESS MVP>

ROger said:
Ken,

Thank you for you help. Unfortunately I am still getting
the #Name? error. I am not understanding why it cannot
find the subform. The name is correct; I have verified it
several times both by recreating the subform and by
checking the name in the properties sheet.

Roger
-----Original Message-----
Try this:

=DCount("*","[Schedule]","[Monday] Between 1 and
700 AND [FADevRyID] = " & Me.FADevRyID)


--
Ken Snell
<MS ACCESS MVP>

I am attempting to do a record count on a column in a
subform. When I use the DCount, I retrieve the total
number of records from the table the subform is
Click to expand...
based
on.
If I use the Count function, I get only the number of
records present in the subform column. Unfortunatly I
cannot use the Count function because I want to exclude
certain values from the count. Here is how my form
Click to expand...
is
set
up:
Main form is FA Schedule, contains employee name and ID
number from the employee table. Subform is Schedule
subform, contains employee schedule information from the
Schedule table. The fields in this table are Time,
Sunday....Saturday.When I use =DCount
("[Monday]","[Schedule]","[Monday] Between 1 and
Click to expand...
700")
in
a textbox located in the subform form footer I
Click to expand...
return
21
records for Monday, which is true if I want the
Click to expand...
number
of
records for all employees. But in this case I only want
the number of records for the current employee displayed,
so the number should actually be 11.Any suggestions on
how I can remedy this problem will be appreciated.
Although I am novice at this, I am not adverse to using
VB or VBA to accomplish my goal.
To verify that I have the correct name of the
Click to expand...
subform,
I
recreated the subform and checked the name.
These are the variations of DCount I have used to
Click to expand...
try
and
puul the information for the current employee from the
table.
=DCount("[Monday]","[Schedule]","[Monday] Between 1 and
700 AND [FADevRyID] = " & [Forms]![Schedule subform]![
FADevRyID]) Returns #Name?
=DCount("[Monday]","Schedule","[Monday]") Returns all
records for Monday from table
=DCount("[Monday]","Schedule","[Monday] Between 1 and
750") Returns all records for Monday from table
=DCount("[Monday]","Schedule","[Monday]=" & Form. [Faculty
Assistant Schedule]) Returns #Name?
=DCount("Monday","[Schedule]","[Monday]= '" & Me.
[Schedule subform] & "'") Returns #Name?
=DCount("[Monday]","Schedule","[Monday] =" &
[Forms].Schedule) Returns #Name?
=DCount("[Monday]","Schedule","[Monday]=" & Form.
[Schedule subform]) Returns #Name?




.
Click to expand...
Click to expand...


.
Click to expand...
 
Time Monday

0700 Class
0800 Class
0900 Class
1000 Class
1100 Available
1200 (blank)
1300 Available
1400 Business 410
1500 Business 410
1600 Business 410
1700 (blank)

This should return 3 because the employee is only working
from 1400 to 1700.
 
DCount can be used as you propose. You "ignore" records by including
criteria in the third argument that will deselect those records from the
list of records returned.

For example, if you wanted to do this using the subform's recordsource as
the "table/query":

NumCounts = DCount("*", Me.SubformName.Form.RecordSource, _
IIf(Me.SubformName.Form.Filter = "", "", Me.SubformName.Form.Filter _
& " And Len([Monday] & """") > 0")


--
Ken Snell
<MS ACCESS MVP>

Roger said:
Thank you Ken. That has me going in the right direction.
I now have the record count for the employee displayed.
Is there a way to get a count on just one column? What I
need to do is ignore the time column and add just those
fields that have data in them that meet a certain
criteria. For example, if Monday is from 7 AM to 5 PM and
has 3 classes scheduled for a total of 3 hours, 2 hours
are marked as Available, and 4 hours marked as Class, I
should only return 3 as my count. I want to ignore the
time column completely. There are also certain data I
want to ignore such as Class, and Available. This is why
I thought I could use the DCount function. I have various
criteria I want the count to be based on.

Thanks
-----Original Message-----
OK -

Let's back up a bit here.

DCount is used to get a count of the number of records in a table or query,
based on a "WHERE" criterion expression that you provide. It will not work
on a subform directly; however, you can use the subform's record source
string as the name of the table or query in the DCount.

If you want to get to the number of records that are displayed on the
subform, let's use the subform's recordsetclone's recordcount property:

NumberOfRecords = Me.SubformName.Form.RecordsetClone.RecordCount

Note that "SubformName" is the name of the subform control (the control on
the main form that holds the subform; this may or may not be the name of the
form that is serving as the subform). The above expression assumes that the
code is running in the main form.

--
Ken Snell
<MS ACCESS MVP>

ROger said:
Ken,

Thank you for you help. Unfortunately I am still getting
the #Name? error. I am not understanding why it cannot
find the subform. The name is correct; I have verified it
several times both by recreating the subform and by
checking the name in the properties sheet.

Roger
-----Original Message-----
Try this:

=DCount("*","[Schedule]","[Monday] Between 1 and
700 AND [FADevRyID] = " & Me.FADevRyID)


--
Ken Snell
<MS ACCESS MVP>

I am attempting to do a record count on a column in a
subform. When I use the DCount, I retrieve the total
number of records from the table the subform is based
on.
If I use the Count function, I get only the number of
records present in the subform column. Unfortunatly I
cannot use the Count function because I want to exclude
certain values from the count. Here is how my form is
set
up:
Main form is FA Schedule, contains employee name and ID
number from the employee table. Subform is Schedule
subform, contains employee schedule information from
the
Schedule table. The fields in this table are Time,
Sunday....Saturday.When I use =DCount
("[Monday]","[Schedule]","[Monday] Between 1 and 700")
in
a textbox located in the subform form footer I return
21
records for Monday, which is true if I want the number
of
records for all employees. But in this case I only want
the number of records for the current employee
displayed,
so the number should actually be 11.Any suggestions on
how I can remedy this problem will be appreciated.
Although I am novice at this, I am not adverse to using
VB or VBA to accomplish my goal.
To verify that I have the correct name of the subform,
I
recreated the subform and checked the name.
These are the variations of DCount I have used to try
and
puul the information for the current employee from the
table.
=DCount("[Monday]","[Schedule]","[Monday] Between 1 and
700 AND [FADevRyID] = " & [Forms]![Schedule subform]![
FADevRyID]) Returns #Name?
=DCount("[Monday]","Schedule","[Monday]") Returns all
records for Monday from table
=DCount("[Monday]","Schedule","[Monday] Between 1 and
750") Returns all records for Monday from table
=DCount("[Monday]","Schedule","[Monday]=" & Form.
[Faculty
Assistant Schedule]) Returns #Name?
=DCount("Monday","[Schedule]","[Monday]= '" & Me.
[Schedule subform] & "'") Returns #Name?
=DCount("[Monday]","Schedule","[Monday] =" &
[Forms].Schedule) Returns #Name?
=DCount("[Monday]","Schedule","[Monday]=" & Form.
[Schedule subform]) Returns #Name?




.
Click to expand...


.
Click to expand...
Click to expand...
 
Couple of possible solutions:

1) Perform the filter FIRST (removing the records you
don't want), THEN count the records.

2) Perform TWO separate query counts. One is the total
of what shows and the other query provides the total of
the records you do NOT want. Then, subtract the latter
from the former.

email me if you need to.
 
Back
Top