I believe the typical approach here would be to reproduce the query using MSQuery instead of Access. That is, if your query in Access is "SELECT X FROM Y WHERE Z" then you want to create a query with MSQuery that is also "SELECT X FROM Y WHERE Z". Then you can use a parameter cell or popup in Excel where the user puts in the date.
It seems to (still) be explained well here:
Edit: hmm. Substitute d-i-c-k for the four asterisks in the URL above.
See if that works for you - if not we can dig into some VBA to force it through one way or another.
Also covered here (perhaps better to start with):
Daily Dose of Excel » Blog Archive » Parameters in Excel external data queries