If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
- Forums
- Question Forums
- Excel Questions
- Thread starterWizardSleeve
- Start dateOct 9, 2014
- Tags
- excelparametersqlvariable
W
WizardSleeve
New Member
- Joined
- Oct 9, 2014
- Messages
- 4
- Oct 9, 2014
- #1
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.
Excel Facts
What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by dateSort by votes
C
CharlesChuckieCharles
Well-known Member
- Joined
- May 10, 2011
- Messages
- 2,155
- Oct 9, 2014
- #2
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")
Upvote0
W
WizardSleeve
New Member
- Joined
- Oct 9, 2014
- Messages
- 4
- Oct 9, 2014
- #3
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.
Upvote0
W
WizardSleeve
New Member
- Joined
- Oct 9, 2014
- Messages
- 4
- Oct 9, 2014
- #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.
Upvote0
P
pechipun
New Member
- Joined
- Nov 28, 2014
- Messages
- 1
- Nov 28, 2014
- #5
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 1For 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!
Upvote0
W
WizardSleeve
New Member
- Joined
- Oct 9, 2014
- Messages
- 4
- Nov 28, 2014
- #6
pechipun said:
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.JUSTIFICACIOFROM
V_PERDUES_CORRECCIONS CWHERE
C.SUBCATEGORIA IN ('CNMNF','CMNF')
AND YEAR(C.ID_DATA_INCIDENT) = YEAR(@DATA)
AND month(C.ID_DATA_INCIDENT) = MONTH(@DATA)
ORDER BY 1For me Doing (like happens with WizardSleeve :
SET @Data = GETDATE() works
Doing SET @Data = '01/10/2014' works
but SET @Data = ? doesn'tIs 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.
Upvote0
C
Comfy
Well-known Member
- Joined
- Dec 21, 2009
- Messages
- 3,386
- Nov 28, 2014
- #7
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')
Upvote0
You must log in or register to reply here.
Similar threads
K
- Question
Export based on criteria and save as single separate workbooks
- Kersh82
- May 15, 2024
- Excel Questions
- Replies
- 0
- Views
- 108
May 15, 2024
Kersh82
K
- Question
VBA Hangs While Producing Complex Graph
- pbornemeier
- Feb 25, 2024
- Excel Questions
- Replies
- 1
- Views
- 225
Feb 25, 2024
frabulator
D
- Question
Excel adodb Operation must use an updateable query
- drag1c
- Nov 23, 2023
- Excel Questions
- Replies
- 2
- Views
- 443
Nov 24, 2023
drag1c
D
A
- Question
Set SQL variable with a cell value
- Arsouille
- Mar 17, 2023
- Excel Questions
- Replies
- 1
- Views
- 373
Mar 18, 2023
rpaulson
K
- Question
How to Use "GetPivotData" formula to retrieve data from a closed workbook
- kizzie37
- Feb 12, 2024
- Excel Questions
- Replies
- 3
- Views
- 661
Feb 12, 2024
RoryA
Forum statistics
- Threads
- 1,218,254
- Messages
- 6,141,403
- Members
- 450,356
- Latest member
- q11
Share this page
- Forums
- Question Forums
- Excel Questions