The solution is actually easier than you imagine, and should not require
coding either in VBA or SQL!

Use two cells in your worksheet to calculate the start and end date of your
query: e.g.
= Today()
= Today() - 8
Now, go into Microsoft Query to edit your query. Show the criteria grid and
drag your date field onto it. Set up a criteria that says something like
this:
Between [StartDate] and [EndDate]
The square braces indicate these are parameters.
Return to Excel - Query will ask for the dates, for now just type something
in.
When back in Excel, right-click on the query results list and choose
"Parameters.." You should see "StartDate" and "EndDate" in a list, and when
you highlight either of them you can specify how you will supply these
numbers. Choose "Get the parameter from the following cell:" and point to
the cell where you put the calculated date.

From then on, when you run your query it will take the calculated dates,
pass them to MSQuery, and return the proper data.

"Raminhos" wrote:

Hi,

Firstly, let me say I don't know how to code. My question is this.

I'm using Excel to query a Lotus Notes Database. I use Microsoft Query to do
it and it works just fine when simply retrieving data.

What I want to do is simply return data based on dates. I want records to be
returned between the current system date and the current system date minus 8
days.

E.g. Return all records between
(1) current system date 04/02/2005 and
(2) current system date 04/02/2005 MINUS 8 days
Expected result would be all records that have a date from and including
04/02/2005 and 27/01/2005

The SQL code that shows up in Microsoft query using the Wizard is this:
SELECT Copy_Of_Closed_."_8"
FROM Copy_Of_Closed_ Copy_Of_Closed_
WHERE (Copy_Of_Closed_.Priority='0 - Critical') AND
(Copy_Of_Closed_.CallType='P') AND (Copy_Of_Closed_.Status='Closed')

This is without the date filtering. As for the " " around the 8 (i.e.
Copy_Of_Closed_."_8"), I'm not sure why it does that, but it works!

I'd really appreciate any help with this and this little tiny piece of code
is stopping me from doing something really really simple!

Many thanks in advance.

Lena Gallagher