Using a cell as a parameter in an OLE DB Connection using an SQL Statement to MS SQL Server

[-cush-]

I am filling a table from a Connection. You can see the connection below along with it's table in the background. The connection passes an SQL Select statement to a MS SQL Server via OLE DB. I would like to modify the query to use the to date in Table 2. I can see a parameter button at the bottom of the definition of the Connection Property window, but it is grayed out. Is there a syntax to type the cell reference right the query? I tried replacing the date in the query with Table2[From Date], but got a syntax error. Is there a special character that I need to put in from of it?

 

[SkipVought]

Hi,

You're using MS Query and there is a way to us Parameter in "simple" queries, which your does not appear to be. BUT you should try and see if this takes.

Right-click in your table and select Table > Edit query. This will take you to the GUI for your query, MAYBE. Look for the SQL button so that you can edit your code. The objective is to replace both Date/Time values in your SQL, with a ? character. Then hit OK. You may need to supply two date parameters ANYTHING really. Finish by File > Return data to Microsoft Excel.

Now in the table, right-click and select Table > Parameters. Now you have a window with 3 options for each of your two parameters. Pick whichever suits your need.

Hope this helps.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[-cush-]

Thanks for the reply. After replacing the Date/Time values with a ? and hitting OK, I get the error "No value given for one or more required parameters". You said I may need to supply two date parameters, but none were ever asked for.

 

[JVFriederick]

KCUSHING

Just a hunch here, but I noticed the database fields are datetime, which are often problematic when pulling dates from XL sheet.

Would suggest you try parameters as (DATETIME > ?-1) AND (DATETIME < ?+1) to get past XL and database compatibility issues.

I would not use the BETWEEN function, it may be the source of your troubles, and the > < operation is quite fast.

Note : Would suggest moving your parameter cells above the query to 'stay out of the way'.

Hope this helps.

JVF

 

[-cush-]

I've tried a few different things, but still get the error "No value given for one or more required parameters". The following will bring back data as well, but as soon as I replace the date with a ?, I get the error.

DECLARE @FromDate datetime, @ToDate datetime
set @FromDate = '1/1/2016';
set @ToDate = '1/1/2017';

SELECT azteca.CA_FEES_VW.CASE_TYPE, azteca.CA_FEES_VW.CASE_NUMBER, azteca.CA_FEES_VW.AMOUNT as FEE, azteca.CA_FEES_VW.PAYMENT_AMOUNT,
azteca.CA_OBJECT.DATE_ISSUED, azteca.CA_FEES_VW.CASE_NAME, azteca.CA_FEES_VW.FEE_CODE, azteca.CA_FEES_VW.FEE_DESC,
azteca.CA_FEES_VW.FACTOR, azteca.CA_FEES_VW.RATE, azteca.CA_FEES_VW.QUANTITY, azteca.CA_FEES_VW.FEE_TYPE_CODE,
azteca.CA_FEES_VW.CASE_STATUS
FROM azteca.CA_FEES_VW INNER JOIN
azteca.CA_OBJECT ON azteca.CA_FEES_VW.CA_OBJECT_ID = azteca.CA_OBJECT.CA_OBJECT_ID
WHERE (azteca.CA_FEES_VW.FEE_CODE IN ('B25PLANREV', 'BPLANREV', 'BREGPLAN')) AND
(azteca.CA_OBJECT.DATE_ISSUED >@FromDate AND azteca.CA_OBJECT.DATE_ISSUED < @ToDate) AND
(NOT (azteca.CA_FEES_VW.CASE_TYPE LIKE 'BC%'))
ORDER BY azteca.CA_FEES_VW.AMOUNT DESC

 

[JVFriederick]

KCUSHING

Previously I did not notice you were pulling the date parameters from a query result. This may be cause of troubles.
To troubleshoot, I would suggest manually typing each date in cells O2 and P2 and testing your query again.
As mentioned in my previous remarks, Excel is problematic when passing dates to queries. Since Excel stores dates as serial numbers it has 'special needs'.

I prefer to use MS Query for my ODBC connections since they can easily be modified. Since I cannot easily replicate what you used
to make your ODBC connection, I am showing the example from a recent MS Query that passes 1 string and 2 date parameters (successfully).

....... bla bla bla .........
WHERE (SDTAR_Schedule.Refinery_Code=?) AND (SDTAR_Schedule.Outage_Start_Date>?-1) AND (SDTAR_Schedule.Outage_Start_Date<?+1)
ORDER BY SDTAR_Schedule.Outage_Start_Date

---------------------------------------------------------------------------------------------------------------

In your SQL I would suggest the removal of all other conditions and focus only on date parameters. Manually type small date intervals to minimize query records in testing.
1) WHERE (DATE(azteca.CA_OBJECT.DATE_ISSUED) > ?) AND (DATE(azteca.CA_OBJECT.DATE_ISSUED) < ?)
or
2) WHERE (CAST(azteca.CA_OBJECT.DATE_ISSUED AS DATE) > ?) AND (CAST(azteca.CA_OBJECT.DATE_ISSUED AS DATE) < ?)

Note the ()'s surrounding each parameter.
Hope this helps.
JVF

 

[dhookom]

Since you are using an OLE DB Query try code like:


[CODE vba]Sub ChangeSQL()
Dim strSQL As String
Dim datFrom As String
Dim datTo As String
datFrom = Range("rngFromDate") 'name your ranges
datTo = Range("rngToDate") 'name your ranges

strSQL = "SELECT [Your fields] " & _
"FROM [Your From with joins] " & _
"WHERE [Your Date Field] BETWEEN '" & datFrom & "' AND '" & datTo & "' " & _
"ORDER BY [Your ORDER BY] "

ThisWorkbook.Connections("Your Connection Name").OLEDBConnection.CommandText = strSQL
ThisWorkbook.Connections("Your Connection Name").Refresh

End Sub
[/code]

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[-cush-]

Thanks for those suggestions. I won't get a chance to get back to this until Monday or Tuesday. I'll let you know.