Can anyone show me where I'm going wrong?

[Numpty]

=DCount("[Status]","Delegate/Booked Course Link","[BookedCourseID] = Forms!Me![BookedCourseID]" & "[Status] = 'Applied'")

I have a subform which displays people who are booked onto a particular course. Along side is their 'Status' which can be changed via a combo box. What I need to do is count the different values from the combo box.
i.e If there are 10 people on a course and 5 have the status applied it will display '5' in text box. The code displayed above is what I have entered into the control source of the text box but to no avail. I can't figure this one out.

 

[Mile-O]

Try this:

=DCount("[Status]","Delegate/Booked Course Link","[BookedCourseID] = Forms!Me![BookedCourseID] AND [Status] = 'Applied'")

 

[Numpty]

Cheers for the reply Mile-O!

Ok, I copied that into the control source but it didn't like it - giving me an #error.

So I changed it slightly to this

=DCount("[Status]","Delegate/Booked Course Link","[BookedCourseID] = Forms!Me![BookedCourseID]" And "[Status] = 'Applied'")

Now it returns a value but rather than counting the number of status fields which are 'Applied' and relate to the BookedCourseID in Me! it is simply counting the number of entries within the underlying table that have a status, regardless of whether it is 'Applied' or any of the other choices.

Gah, its ten past four on a friday and I'm far too confused!

 

[Mile-O]

Try this then:

=DCount("[Status]","Delegate/Booked Course Link","[BookedCourseID] = " & [BookedCourseID] & " And [Status] = 'Applied'")

 

[Numpty]

Yeeeeeessss!!!!

Thats worked a treat mate! Really appreciate that Mile-O. I can go home happy now!!

 

[Mile-O]

You can't put Me! in the ControlSource.

I didn't spot it the first time

And remember the textbox is on a form and having it within a string can't reference it.

 

[Numpty]

OK, thanks.

Still new to Access and learning all the time.

I can't stress how useful this site is.