How do you create a SQL query in Excel 2007 with a dynamic date range?

Question

I am trying to create a reporting spreadsheet that can print reports for a given time period. The query below works, but when I try to use a ? parameter in place of the date, I get an error after selecting a cell containing my date. If I use single quotes ('?') I get a conversion from string to date/time failure, if I don't (?) I get a syntax error near @p1.

Eventually I will need either a start and end date or a formula adding a month or shift to the starting date/time to filter the data down to important information. The query was built in Microsoft Query.

SELECT FloatTable.DateAndTime,
       TagTable.TagName
FROM   master.dbo.FloatTable FloatTable,
       master.dbo.TagTable TagTable
WHERE  FloatTable.TagIndex = TagTable.TagIndex
       AND (( FloatTable.DateAndTime >= {ts '2012-06-01 00:00:00'} )) 

Any assistance would be much appreciated.

Solution

You'll need to enter the query as a formula instead of as text, and build the date from the cell you've selected so that a valid sql statement is created.

Assuming you have a date in cell A1, put the following formula in cell B1:

="SELECT FloatTable.DateAndTime, TagTable.TagName FROM master.dbo.FloatTable _ 
FloatTable, master.dbo.TagTable TagTable WHERE FloatTable.TagIndex = _
TagTable.TagIndex AND ((FloatTable.DateAndTime>={ts '" & YEAR(A1) & "-" _
& MONTH(A1) & "-" & DAY(A1) & " 00:00:00'}))"

Note that the underscores at the end of each line indicate continuation. You'll need to delete them and the carriage returns for the formula to work. The entire formula is set up on a single row below. (Hard to read on this site, but Excel likes it!)

="SELECT FloatTable.DateAndTime, TagTable.TagName FROM master.dbo.FloatTable FloatTable, master.dbo.TagTable TagTable WHERE FloatTable.TagIndex = TagTable.TagIndex AND ((FloatTable.DateAndTime>={ts '" & YEAR(A1) & "-" & MONTH(A1) & "-" & DAY(A1) & " 00:00:00'}))"