How do you automatically update criteria in MS Query

G

Guest

Hi

I am pulling data out of an ODBC database into Excel using MS Query. I would like to write a criteria where the query includes only the previous 12 months of data (TODAY()-365). Is this possible?

TIA
 
K

K Dales

Yes, very possible and handy: First, in Excel, put your
formula =(TODAY()-365) into a cell. Then, in MSQuery, put
the appropriate date field into the criteria grid and on
the criteria line enter a parameter, e.g. [StartDate].
Then "Return Data to Excel" and when the Query Properties
box comes up choose "Parameters..." You will get a dialog
box that allows you to specify how MSQuery will interpret
the parameter StartDate. Tell it to get the value from a
cell and specify the cell containing your formula.
-----Original Message-----
Hi

I am pulling data out of an ODBC database into Excel
using MS Query. I would like to write a criteria where
the query includes only the previous 12 months of data
(TODAY()-365). Is this possible?
 
J

Jamie Collins

...
Yes, very possible and handy: First, in Excel, put your
formula =(TODAY()-365) into a cell.

Your database server should more accurately provide this info, rather
than obtaining it locally e.g.

Jet ('MS Access'):

SELECT Now

SQL Server:

SELECT GetDate()

You could use this in a query e.g. for Jet:

SELECT
RefID, DateEffective, Earnings
FROM
EarningsHistory
WHERE
DateEffective BETWEEN
DateSerial(Year(Now)-1, Month(Now), Day(Now))
AND Now
;

Jamie.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top