[AccessD] Filtering Form by Subform Value

Dear Group:

I was wondering if someone could tell me the syntax for filtering a form
based upon a value in a subform?

I know that the generic syntax for a filter is:
Me.Filter = "(([recordsource].[field] = 'something' )) "

I know that if you want to filter for something in a drop down box it
is:
Me.filter = ((Lookup_[Name_of_DropDown].[field]="text of dropdown"))

(When I say "text of dropdown" I mean the visible column whatever that
may be)

What I would like to do is something similar to that, 
but filter on a field on a subform instead of on the
text of a dropdown box.

What I have is a form with customer records;
And a subform with sales records (only one sale per customer).
The sales records have a customer_ID which is how the two are linked.

How do I write the filter so I get all the customers with a sales record
date = #mm/dd/yy#

Me.filter = ((Formname_SubformName__NameOfDateBox.[DateField] =
#MM/DD/yy#))

Me.filter = ((customerID in SELECT sales.Customer FROM sales WHERE
salesDate = #mm/dd/yy#))

These syntax do not work.

Am I on the right track or is this not possible without redoing the
source query to the form?

Thanks

-Z

Stephen R. Zayko, P.E.

SECOR International Inc.
2321 Club Meridian Drive, Ste E
Okemos, MI 48864 

(p) (517) 349-9499 ex24
(f) (517) 349-6863
(m) (517) 204-5136

szayko at secor.com
www.secor.com