Excel, reference a cell for parameter in SQL to import data
 

I am using SQL in Microsoft Query to import data from a ODBC data source
into Excel. I am linking mutiple tables from the source and this seems
to prevent from being able to reference a cell in the spreadsheet as a
parameter value. The import works when I 'hard-code' the date in the
code, but I would like to be able to reference a cell in the spreadsheet
so the end user can refresh the data after just changing the date in the
spreadsheet. Here's a portion of my code:

SELECT trim(gltrans.ref), GlTrans.Value
FROM GlTrans INNER JOIN SYSFIL
ON (GlTrans.SUB_ACCT = SYSFIL.DB_SUB_ACCT)
AND (GlTrans.ACCT = SYSFIL.DB_ACCT)
AND (GlTrans.DEPT = SYSFIL.DB_DEPT)
AND (GlTrans.BRANCH = SYSFIL.DB_BRANCH)
AND (GlTrans.COY = SYSFIL.DB_COY)
WHERE GlTrans.Period_Date < '2006-06-01' AND
(SYSFIL.Module_Type='VHS' AND SYSFIL.Acc_des='RT') AND
GlTrans.Year_End_Date =
(SELECT current_year_end_date FROM company WHERE coy = (SELECT db_coy
FROM sysfil WHERE acc_des = 'debtor'));

What I would like to do is replace the '2006-06-01' date with the date
supplied in cell C1 in my spreadsheet. I do have the date formatted
correctly in the cell.

I have searched for resolutions on the Internet, trying several
examples, but have not found the correct resolution yet. Any help is
appreciated.
Brian
Business Analyst
JDIS

*** Sent via Developersdex http://www.developersdex.com ***

Excel, reference a cell for parameter in SQL to import data
 

Use square brackets in thew statement. When you refresh the table, it
will prompt you for a value and give you the option to select a range
and save it (or a value) in the query.

WHERE GlTrans.Period_Date < [ ]

You can also edit parameters from the data menu - Dat,Import External
Data, Paramaters.

If you are using the visual interface to create queries you can enter
the square brackets in the dialog box as the value when you're choosing
criteria.

Excel, reference a cell for parameter in SQL to import data
 

Sorry - in the actual sql statement, use a question mark:

WHERE
((X.X_DATE=?)

Excel, reference a cell for parameter in SQL to import data
 

WHERE (GlTrans.Period_Date < ?)

Excel, reference a cell for parameter in SQL to import data
 

Thanks for your suggestions, but I continue to get errors...
When I put "WHERE (GlTrans.Period_Date < ?)" I get the error message
"Parameters are not allowed in queries that can't be displayed
graphically".
If I put the questionmark in single quotes I get an error message
"Cannot convert ? to a time stamp".
If in double quotes I get a "Column '?' not found error.

Any other thoughts?
Brian
Business Analyst
JDIS

*** Sent via Developersdex http://www.developersdex.com ***