I've dabbled with record sets, but am not proficent. are you saying to scrap
the Dcount? Would you be able to suggest some code, or perhaps a framework of
how its structured. It's getting late know, so I'll perhaps have to try it
tomorrow.
Thanks again for all your help.
Winger
:
There are a couple of thoughts I have.
First, the reason you are getting all events is you are running the DCount
on the entire table. It knows nothing of the filtering you are doing in your
subform.
You can do one of two things. Add filtering to limit the count to the the
event. A better solution would be to base the count on the subform's
recordsetclone. It should be faster because you are searching a smaller
dataset.
I don't know how many rows are in your table, by 5 counts running against it
at the same time would really make it look like it is locked up.
--
Dave Hargis, Microsoft Access MVP
:
It was the space:
=DCount("*","[tblAttendees]","Nz([WK1],'No') = 'Yes'")
The above line works......
Unfortunatley I've spotted two issues.
1) I was testing this on a SubForm - which gives me all the values in the
database, but of course when I go to the main form, I still get all the
values on the database and not just the "yes" for the partcualr event I'm
currenlty showing on the main form.
2) Also, on the subform, I actually replicated the formula for Wk1-Wk5 which
works OK for for another field which has larger values (about 1000 Yes's), I
think its locking up the system.
I can do a work around for 2) but number 1) I need to solve,
Can the formula be adapted to ref just the values shown on the subform?
Winger
:
How about if you changed the field to a Yes/No data type?
--
Dave Hargis, Microsoft Access MVP
:
I do have a back up plan!
As =Count([WK1]) works, except it adds the "no's" as well, I could remove
all the no's, but we do actully find them useful.
:
still "#Error" msg using:-
=DCount("*"," [tblAttendees]","Nz([WK1],'No') = 'Yes'")
!!
(thanks for your efforts on this by the way)
Winger
:
oops,
=DCount("*"," [tblAttendees]","Nz([WK1],'No') = 'Yes'")
my bad. notice change to single qoutes around 'No'
--
Dave Hargis, Microsoft Access MVP
:
got an error message about invalid syntax in the expression, but not sure
what's wrong.
=DCount("*"," [tblAttendees]","Nz([WK1],"No") = 'Yes'")
??
:
I don't know if this will resolve the problem, but try:
=DCount("*"," [tblAttendees]","Nz([WK1],"No") = 'Yes'")
I'm thinking the Nulls may be causing the problem.
--
Dave Hargis, Microsoft Access MVP
:
An "#error" message appears in the field.
The field is a text field, and has blanks as well as "No"s.
I started using =Count([Wk1]) but this returned all the non blanks, and I
don't think I can use CountIF.
I tried putting:-
=DCount("*"," [tblAttendees]","[WK1] = 'Yes'")
but get the same error.
Any more clues?
:
You did not say what was not working, or why the value returned seems to be
incorrect. I will start with a couple of pointers, and then post back with
what is going on.
First, with the DCount, you don't need to specify a field name in the first
arguement. It will, in fact, degrade performance. The value in the field is
meaningless anyway. Next, your comparison is suspect.
=DCount("*"," [tblAttendees]","[WK1] = 'Yes'")
If the [WK1] is a text field and you expect to find the value "Yes", then it
should be fine.
If it is Yes/No field, then comparing it to a text value will not work.
Neither would "[WK1] = Yes"). Yes is not a substitute for True.
--
Dave Hargis, Microsoft Access MVP
:
I'm trying to create a calculated field in the header of a form that counts
up the number of people that attended a course and have a "Yes" in the
attendance field.
As the field control source I'm trying to use:-
=DCount("[Wk1]"," [tblAttendees]","[WK1] = 'Yes' ")
I've looked through previous threads and many refer to more complex examples
using dates, but I'm afraid I still haven't grasped it.
Any advice greatly appreaciated.
thanks
Winger
