DCOUNT THINK INNER JOIN QUESTION 1

[gingernob]

Hi

i have a parent form: frmExams with a subform frmINVIGILATORS

The subform represents the contents of a table INVIGACTIVITY. The master field link is via EID field (exam ID)

im trying to do a dcount on the afterupdate event of the subform, which does work with the following:
[Forms]![frmexams]![txtallocnumsup].Value = DCount("[INVIGID]", "INVIGACTIVITY", "[EID]=" & [Forms]![frmexams]![EID] & " AND [BRID]=1")

but then I realised in the INVIGACTIVITY table it only references the INVIGID (reference from INVIGILATORS table). In the INVIGILATORS table there is a field which represents whether they are a supervisor or standard invigilator.
So finally; my question is:
how, rather than say return 6 records which may be 4 invigilators and 2 supervisors, how can i restrict the result to just the 2 supervisors where the INVIGILATORS.INVIGSTATUSID = 1 (1 = SUPERVISOR, 2 = standard INVIGILATOR)

Appreciate your assistance as this is driving me completely bonkers!
David

 

[MajP]

Build a new query that has a where statement like
"where Brid = 1 AND INVIGSTATUSID = 1"

DCount("[INVIGID]", "aNewQuery", "[EID]=" & [Forms]![frmexams]![EID]")

 

[gingernob]

PERFECT!!!!!!!
EXACTLY what i was looking for..really appreciate swift response.