1

I tried working with the solutions noted in Excel: Use a cell value as a parameter for a SQL query and How to add parameters to an external data query in Excel which can't be displayed graphically?. However, I can't get any of the solutions to work.

Below is a segment that I am having trouble with. There are multiple tables in the query. I pasted the SQL in the Command text in the Definition tab of the Connection Properties window. I have no problems when I enter the dates as they are below. However, I get a message about converting date and/or time from character string when I replace the dates with question marks. If I do the same and exclude the quote marks, I get a message about invalid parameter number.

I'm thinking that I should try to programatically update the connection file because I can't figure out how to acheive what I want to do any other way. 

AND CAST(EXITED_FROM_ACTIVITY_DATE AS DATE) BETWEEN '2014-12-01' AND '2014-12-01'
Improve this question
2
  • What happens if you also cast the parameters as dates? Commented Dec 29, 2014 at 21:17
  • I don't know. I've given up on that method. It seems like it is going to be too difficult to do with to many exceptions. I used the macro recorder when creating a MS Query. Here is some information on the Query Table object: msdn.microsoft.com/en-us/library/office/… Commented Jan 2, 2015 at 14:18

1 Answer 1

Reset to default
0

You can use date format as in query like this: 

TO_CHAR(A.JOURNAL_DATE ,'YYYYMM') >='201506' and TO_CHAR(A.JOURNAL_DATE ,'YYYYMM') <='201506'

and can give parameters as 201501 1 can be extended period 1 to 12

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.