Hi, I have a large SQL statement that uses 2 variables from an excel worksheet for start_date and end_date
I call the variables in the temp table at the top of the statements and then again in the main statement.
If I leave out the temp table, It works fine but when it is included I get "invalid parameter number" and invalid descriptor index" as errors.
The WHERE of my temp table is as follows:
WHERE
invoice_booking_detail.customer_group = '12' AND
invoice_booking_remarks.remark_number = 3 AND
invoice_booking_detail.invoice_date BETWEEN ? AND ?)
and in my main statement is:
WHERE
invoice_booking_detail.invoice_date >= ? AND
invoice_booking_detail.invoice_date <= ? AND
invoice_booking_detail.customer_group = '12'
having never really used temp tables before, I am a little in the dark.
IPerhaps the "format" of the date you are passing is wrong?
SQL will work best with a date in the format '20160111', Excel may well be providing an integer (number of days since Epoch, or something like that). Perhaps try formatting the date into 'yyyymmdd' (including the single quotes) and seeing if that works?
Maybe just put a Text String in the cells used for Start/End Date to start with, to be sure that is how it arrives in SQL, and then graduate to allowing user to enter a "pretty" date, in a different cell, and formatting that into the required format in the actual cell used for the SQL parameter
(Might be you are not using a Cell for the parameters, but rather getting a prompt for each parameter, in which case you should just be able to enter an 'yyyymmdd' formatted answer. I'm not sure if you need the single quotes, my guess is that you will, so I suggest you try both)