Judi said:
I'm using MSQuery in Excel to get information from an external
database. Is there any way to have the criteria look at the current
date and return all records for the last 90 days or 6 months? I can't
see a way to use a "NOW" function.
Judi,
One option would be to modify your query to accept parameters. Note
that this is from memory, if something doesn't work quite right just
ask.
In the criteria portion of MS Query type in >=[StartDate] And
<=[EndDate] for the field that contains the dates (change StartDate
and EndDate to something else if they happen to match field names in
the tables you are looking at). When you return to Excel, it will
prompt you for a date for each of these parameters. Once the query is
done updating, right click somewhere in the query table and choose
"Parameters. . .". The third option allows you to connect each
parameter to a cell on the worksheet.
The cells that you link to could use the NOW function to get the range
of dates that you need. It can also be set up to refresh the query
whenever one of these cells changes. It might not hurt to add a
button to refresh the data if other people are going to be using this
file.
Hope this helps,
Mike
