Excel/SQL - Parameter in Variable

[WizardSleeve]

I have built a report and, as an example, one of the tables uses unions to bring in various sets of information.

Previously they just wanted to look at the last 6 months, which was fine as I could use the getdate() function to bring back the data to Pivot within excel.

They have since decided they want to select the date that they would like to view six months up until.

Rather than add 7 parameters in for this table linked back to the same cell reference, then repeat this for other tables I though I could use a variable + a parameter

I simply did:
DECLARE @USEDATE AS DATETIME
SET @USEDATE = ?

? references a cell with a date, and I have tried numerous formats for this, but I keep getting the following errors:

Invalid Parameter Number
Invalid Descriptor Index

Putting in a date directly seems to work but using a parameter doesn't

Am I doing something wrong?
Any help is much appreciated! - thank you in advance.

 

[CharlesChuckieCharles]

Its sound like CODE formatting


You need to post your code (most of it )

But this may indicate the solution

SQLStr = "DECLARE @USEDATE AS DATETIME " & vbnewline

SQLStr = SQLStr & "SET @USEDATE = " & format(Range("A1").value,"DD/MM/YYYY")

 

[WizardSleeve]

Hi, thanks for the response.

I am not doing it in VB though, I've but the query through Microsoft Query (without the wizard)

Doing SET @USEDATE = GETDATE() works
Doing SET @USEDATE = '2014-10-01' works
but any variation of SET @USEDATE = ? doesn't

for example I tried using TEXT, FORMAT, CAST etc in the parameter field but no joy.

 

[WizardSleeve]

Sorry missed the bit where you wanted me to post my code

The who query works apart from when trying to use a Parameter in there.

The cell it is referencing is a formula that is a date
I have tried various formats on the cell including TEXT to format it into a more recognised YYYY-MM-DD

If you think posting the whole query will help, I will, but it is purely the parameter section that doesn't work.

 

[pechipun]

Hi,
I have the same problem with declared parametres using "?" .
In my case I'm trying this query:

SET NOCOUNT ON
DECLARE @DATA DATETIME
SET @DATA = ?

SELECT
C.ID_DATA_INCIDENT ,
C.REGISTRE ,
NULLIF(C.VOLUM_MESURAT+C.VOLUM_NO_MESURAT,0) ,
C.JUSTIFICACIO

FROM
V_PERDUES_CORRECCIONS C

WHERE
C.SUBCATEGORIA IN ('CNMNF','CMNF')
AND YEAR(C.ID_DATA_INCIDENT) = YEAR(@DATA)
AND month(C.ID_DATA_INCIDENT) = MONTH(@DATA)

ORDER BY 1

For me Doing (like happens with WizardSleeve :
SET @Data = GETDATE() works
Doing SET @Data = '01/10/2014' works
but SET @Data = ? doesn't

Is there any solution?
Thanks very much for your help!

 

[WizardSleeve]

Hi,
I have the same problem with declared parametres using "?" .
In my case I'm trying this query:

SET NOCOUNT ON
DECLARE @DATA DATETIME
SET @DATA = ?

SELECT
C.ID_DATA_INCIDENT ,
C.REGISTRE ,
NULLIF(C.VOLUM_MESURAT+C.VOLUM_NO_MESURAT,0) ,
C.JUSTIFICACIO

FROM
V_PERDUES_CORRECCIONS C

WHERE
C.SUBCATEGORIA IN ('CNMNF','CMNF')
AND YEAR(C.ID_DATA_INCIDENT) = YEAR(@DATA)
AND month(C.ID_DATA_INCIDENT) = MONTH(@DATA)

ORDER BY 1

For me Doing (like happens with WizardSleeve :
SET @Data = GETDATE() works
Doing SET @Data = '01/10/2014' works
but SET @Data = ? doesn't

Is there any solution?
Thanks very much for your help!

The way I got around my issue was running the code in VBA and pasting the recordset into the table rather than refreshing a table from Microsoft query.

It would appear excel has trouble handling parameters, especially when date fields

I think this is because the query reads the date as a number where as in the macro you can set it to be a string and format the text how you want.

 

[Comfy]

As has been pointed out the date within Excel is actually a number.

I'm a complete beginner when it comes to SQL but I had a similar issue and resolved it (if I remember correctly) using the following.


WHERE FORMAT(Date_Column, 'dd/mm/yy') >= FORMAT(?, 'dd/mm/yy')