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'
Community
  • 1
  • 1
Tyrone
  • 77
  • 1
  • 10
  • What happens if you also cast the parameters as dates? – Doug Glancy Dec 29 '14 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: http://msdn.microsoft.com/en-us/library/office/ff837764(v=office.14).aspx – Tyrone Jan 02 '15 at 14:18

1 Answers1

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

CubeJockey
  • 2,209
  • 8
  • 24
  • 31
partha
  • 1