How do you automatically update criteria in MS Query
 

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

How do you automatically update criteria in MS Query
 

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?

TIA
.

How do you automatically update criteria in MS Query
 

"K Dales" wrote ...

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?

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.

--