excel - access query - date range
 

I am trying to figure out how i can run an access query in excel and
change the date range of the query without altering the query itself.

i have written the query


Formula:
--------------------
SELECT Payments.id, Payments.Name, Payments.PaymentID, Payments.PaymentDate, Payments.`ex vat`, Payments.`ex vat`*0.175 AS 'vat', (Payments.`ex vat`*0.175)+Payments.`ex vat` AS 'total'
FROM table
WHERE (Payments.PaymentDate<={ts '2004-12-01 00:00:00'} And Payments.PaymentDate={ts '2004-08-01 00:00:00'})
--------------------


and have managed to successfully import the data into excel. What is
the easiest way to allow a date range to be added without the need to
alter the query ie is it possible to pass variables to the query - so
the query would be something like



Formula:
--------------------
SELECT Payments.id, Payments.Name, Payments.PaymentID, Payments.PaymentDate, Payments.`ex vat`, Payments.`ex vat`*0.175 AS 'vat', (Payments.`ex vat`*0.175)+Payments.`ex vat` AS 'total'
FROM table
WHERE (Payments.PaymentDate<= $end And Payments.PaymentDate= $start )
--------------------


many thanks
mxp


--
mxp
------------------------------------------------------------------------
mxp's Profile: http://www.excelforum.com/member.php...o&userid=30994
View this thread: http://www.excelforum.com/showthread...hreadid=506755

excel - access query - date range
 

hi mxp

Look on this page

You can use a cell with the date or see the example file where you can save different criteria.
http://www.rondebruin.nl/accessexcel.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"mxp" wrote in message ...

I am trying to figure out how i can run an access query in excel and
change the date range of the query without altering the query itself.

i have written the query


Formula:
--------------------
SELECT Payments.id, Payments.Name, Payments.PaymentID, Payments.PaymentDate, Payments.`ex vat`, Payments.`ex vat`*0.175 AS
'vat', (Payments.`ex vat`*0.175)+Payments.`ex vat` AS 'total'
FROM table
WHERE (Payments.PaymentDate<={ts '2004-12-01 00:00:00'} And Payments.PaymentDate={ts '2004-08-01 00:00:00'})
--------------------


and have managed to successfully import the data into excel. What is
the easiest way to allow a date range to be added without the need to
alter the query ie is it possible to pass variables to the query - so
the query would be something like



Formula:
--------------------
SELECT Payments.id, Payments.Name, Payments.PaymentID, Payments.PaymentDate, Payments.`ex vat`, Payments.`ex vat`*0.175 AS
'vat', (Payments.`ex vat`*0.175)+Payments.`ex vat` AS 'total'
FROM table
WHERE (Payments.PaymentDate<= $end And Payments.PaymentDate= $start )
--------------------


many thanks
mxp


--
mxp
------------------------------------------------------------------------
mxp's Profile: http://www.excelforum.com/member.php...o&userid=30994
View this thread: http://www.excelforum.com/showthread...hreadid=506755