DCount in a form

  • 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 frmSchedule,
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. Thank
you,
 
Roger,

Try the following...

=DCount("[Monday]","[Schedule]","[Monday] Between 1 and 700
AND [EmployeeID] = " &
[Forms]![frmYourFormName]![EmployeeID])

Gary Miller
Sisters, OR
 
Gary,

Thank you for your suggestion. Unfortunately I am still
unable to get the data I need. I keep getting the #Name?
error. Just to be sure I went ahead and recreated the
subform to verify the correct name. I get the same
results. Posted below are all the variations I have
tried. Any other suggestoins you may have will be greatly
appreciated. This count is the only thing keeping me from
proceeding with the project.

=DCount("[Monday]","[Schedule]","[Monday] Between 1 and
700
AND [FADevRyID] = " & [Forms]!Schedule![ 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
& "'") Returns #Name?
=DCount("[Monday]","Schedule","[Monday] =" &
[Forms].Schedule) Returns #Name?
=DCount("[Monday]","Schedule","[Monday]=" & Form.[Faculty
Assistant Schedule]) Returns #Name?
-----Original Message-----
Roger,

Try the following...

=DCount("[Monday]","[Schedule]","[Monday] Between 1 and 700
AND [EmployeeID] = " &
[Forms]![frmYourFormName]![EmployeeID])

Gary Miller
Sisters, OR


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 frmSchedule,
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. Thank
you,
Click to expand...


.
Click to expand...
 
Back
Top