Excel/SQL - Parameter in Variable

WizardSleeve

New Member
Joined
Oct 9, 2014
Messages
4
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.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

CharlesChuckieCharles

Well-known Member
Joined
May 10, 2011
Messages
2,153
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

New Member
Joined
Oct 9, 2014
Messages
4
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

New Member
Joined
Oct 9, 2014
Messages
4
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

New Member
Joined
Nov 28, 2014
Messages
1

ADVERTISEMENT

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!:rolleyes:
 

WizardSleeve

New Member
Joined
Oct 9, 2014
Messages
4
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!:rolleyes:

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

Well-known Member
Joined
Dec 21, 2009
Messages
3,377
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')
 

Watch MrExcel Video

Forum statistics

Threads
1,108,910
Messages
5,525,585
Members
409,651
Latest member
Quasar Hunter

This Week's Hot Topics

Top