Filter By SubForm Date Field

[mabiem]

Hi,

I have a form that is based on a linked table of students eligible to complete a online survey. Within that form, I have a sub form (based on another linked table) of students who have completed the form and at what date and time.

Only about 1 in 10 eligible students complete the form.

I'd like to filter by students who have completed the form, and sort by date submitted.

So I have the main form called "students" and the subform with the date submitted is called "New - BasicInfo subform" and they are linked by student ID. The date submitted field in the subform is formatted like "5/6/2013 3:07:32 PM".

I've tried adding this to me "filter" field in the STudents form properties:

Between [Students]![New - BasicInfo subform]![05/06/2013] AND [Students]![New - BasicInfo subform]![10/01/2013]

No luck, I get syntax error missing operator. ANy sugestions?

Thanks,

Mike

 

[billmeye]

Are you only wishing to filter the subform by some date range and find out who has completed the survey? If so, linking the main form and subform by StudentID does not make sense, it restricts the subform recordset to show only that single student. Otherwise, with the subform not linked to the mainform, set the record source to include the date range criteria:

SELECT *
FROM studentsSuvery
WHERE (((studentsSuvery.DateCompleted) Between [Students]![DateFrom] And [Students]![DateTo])) ORDER BY studentsSuvery.DateCompleted asc;

This assumes your date selection fields are on the main form. In each date field AfterUpdate event you need to requery the subform Me![New - BasicInfo subform].requery

Let me know better what you are trying to accomplish with your form setup. You may need to brake things up in another way.