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
SELECT Companyid,[debitamt]-[crdtamnt] as Amount, 
FROM vwCompany
WHERE [TRXDate] between '4/1/2015' and '4/30/2015' 

Open in new window


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"'

Open in new window

KANEDA 0149Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
I would think that the single quotes are creating a string that doesn't represent a date.  My first suggestion would be to remove the single quotes, and the the double quotes, so that you have this:

SELECT Companyid,[debitamt]-[crdtamnt] as Amount,
FROM v


wCompany
WHERE [TRXDate] between Worksheets("Parameter").Range("B2").Value and
                                                 Worksheets("Parameter").Range("B3").Value
0
KANEDA 0149Author Commented:
Hi Bob, apologizes for the delayed response.  I tried that and removed the single and double quotes but I got the error message 'Worksheets' is not a recognized built-in function name.
0
Bob LearnedCommented:
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.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

KANEDA 0149Author Commented:
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;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=[DBNAME];Data Source=[SQLNAME];Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=[SQLUSERNAME];Use Encryption for Data=False;Tag with column collation when possible=False

Command Type: SQL

Command Text:    SELECT Companyid,[debitamt]-[crdtamnt] as Amount,
                                FROM vwCompany
                                WHERE [TRXDate] between '4/1/2015' and '4/30/2015'
0
G GodwinDatabase AdministratorCommented:
AFAIK you can't reference Excel cells like that in the CMD query.

I have always done it like this:
SELECT Companyid,[debitamt]-[crdtamnt] as Amount, ...
FROM vwCompany
WHERE [TRXDate] between ? and ?

Open in new window


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
0
KANEDA 0149Author Commented:
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.

image.jpg
0
G GodwinDatabase AdministratorCommented:
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.

DataConnection.jpg
0
G GodwinDatabase AdministratorCommented:
When you change your query to this:

 DataConnection2.jpg
it should create two parameters for you named "parameter1" and "parameter2".

You should then be able to select the cell you want to use as the source for each of them.

ALSO, I can't see your whole query, but if that's a comma after AMOUNT, and nothing follows except FROM, that will cause an error.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KANEDA 0149Author Commented:
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.

C--Users-kwhitley-Pictures-Untitled.jpg
0
Martin LissOlder than dirtCommented:
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".
0
KANEDA 0149Author Commented:
It was a good effort, thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.