Excel SQL Data Connection Reference Cell with Date Value
I have an existing SQL data connection in Excel using a SQLOLEDB.1 connection string with command type as SQL and command text below. In the WHERE clause I have set the values as '4/1/2015' and '4/30/2015' which works just fine. I'm try to change the parameter to reference a cell value in B2 (From Date) and B3 (To Date) instead so I don't have to keep updating the connection string. I used the following syntax but when I try to run the connection but I get the error "Conversion failed when converting date and/or time from character string. Cells B2 and B3 are formatted as General. Can someone help!
ORIGINAL Command Text
MODIFIED Command Text
ORIGINAL Command Text
SELECT Companyid,[debitamt]-[crdtamnt] as Amount,
FROM vwCompany
WHERE [TRXDate] between '4/1/2015' and '4/30/2015' MODIFIED Command Text
SELECT Companyid,[debitamt]-[crdtamnt] as Amount,
FROM vwCompany
WHERE [TRXDate] between '"Worksheets("Parameter").Range("B2").Value"' and '"Worksheets("Parameter").Range("B3").Value"'SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OK, so much for the easy guess. How are you executing that SQL statement? I believe that we need to have parameters or variables at that point. I am just not sure of the execution context.
ASKER
I'm using Excel's Data Connection with the following properties set on the Definition tab outlined below. It works fine when using
Connection Type: OLE DB Query
Connection String: Provider=SQLOLEDB.1;Integr
Command Type: SQL
Command Text: SELECT Companyid,[debitamt]-[crdt
FROM vwCompany
WHERE [TRXDate] between '4/1/2015' and '4/30/2015'
Connection Type: OLE DB Query
Connection String: Provider=SQLOLEDB.1;Integr
Command Type: SQL
Command Text: SELECT Companyid,[debitamt]-[crdt
FROM vwCompany
WHERE [TRXDate] between '4/1/2015' and '4/30/2015'
AFAIK you can't reference Excel cells like that in the CMD query.
I have always done it like this:
Then you go back to the parameters list in Excel and point to the cell for each parameter.
As long as the cell can be converted to the proper type for the query, it should work.
e.g. It works when I use mm/dd/yyyy but not when I use yyyymmdd in the cell.
-GDG
I have always done it like this:
SELECT Companyid,[debitamt]-[crdtamnt] as Amount, ...
FROM vwCompany
WHERE [TRXDate] between ? and ?Then you go back to the parameters list in Excel and point to the cell for each parameter.
As long as the cell can be converted to the proper type for the query, it should work.
e.g. It works when I use mm/dd/yyyy but not when I use yyyymmdd in the cell.
-GDG
ASKER
Thanks GDG_DBA, unfortunately I don't have an option to add or reference the cells as parameters. It is greyed out for me. Even when I try to create a new Other External Source and step through the wizard on the OLE DB for SQL it never prompts me for a parameter or to add one. Below is a screenshot of the parameter button greyed out.
My option goes away also if I don't have parameter placeholders ("?") in the query.
Try adding the placeholders. Then save and reopen the query. I bet you will be able to edit the parameters and point to the cells.
Try adding the placeholders. Then save and reopen the query. I bet you will be able to edit the parameters and point to the cells.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No dice GDG_DBA, I did take out the extra comma after Amount. I get the error "No given value for one or more requirements". It's still greyed out.
I've requested that this question be deleted for the following reason:
The question has either no comments or not enough useful information to be called an "answer".
The question has either no comments or not enough useful information to be called an "answer".
ASKER
It was a good effort, thanks!
ASKER