Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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=?) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel, reference a cell for parameter in SQL to import data
WHERE (GlTrans.Period_Date < ?) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import data from an Access parameter query | Excel Discussion (Misc queries) | |||
Data_field parameter in getpivotdtata as a cell reference. | Excel Discussion (Misc queries) | |||
Help to import data from reference workbook | Excel Discussion (Misc queries) | |||
Data Import Cell Reference | Excel Programming | |||
Import Access records to excel (parameter is a called funct) v.20 | Excel Programming |