Excel 2010 -- adding date parameters to the SLQ query via the Connections properties

[olrac]

Hi,

I'm very new to VBA for EXCEL 2010.

I wrote a query with a where clause :

WHERE

TC.TimeCardDateTime = '2015-07-08'
and then a VBA piece:

Private Sub RefreshQuery()

With ActiveWorkbook.Connections("MSP_RECON").OLEDBConnection
.CommandText = "EXECUTE TC.TimeCardDateTime '" & Range("D2").Value & "'"
End With
ActiveWorkbook.Connections("MSP_RECON").Refresh


End Sub

Question 1 : The VBA codes gives error Subscript out or Range
Question 2 : How to I modify the VBA go look at a range of rates ( Where TC.TimeCardDateTime between '2015-01-01' and '2015-10-12'

Thank you very much!

 

[SkipVought]

Hi,

I have never used OLEDB. For the past 20 or so years, I have used ODBC regularly, since I have the entire SQL string to manipulate.

What kind of database are your querying via OLEDB?

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

 

[olrac]

Hi,

Thanks for the quick response. I added a file to my original post (and again in this one). I database is a SQL database (on a MS 2013 server). In EXCEL I just choose Command Type as SQL and add my query.

From what I understand of the VBA..


StartDate = Sheets("Sheet1").Range("D2").Value 'Pass value from cell D2 to StartDate variable
EndDate = Sheets("Sheet1").Range("D3").Value 'Pass value from cell D3 to EndDate variable

'Pass the Parameters values to the Query used in the Data Connection
With ActiveWorkbook.Connections("MSP_RECON").OLEDBConnection
.CommandText = "EXEC TC.TimeCardDateTime between '" & StartDate & "' AND '" & EndDate & "'"
ActiveWorkbook.Connections("MSP_RECON").Refresh



it looks at the Query and replaces the String "TC.TimeCardDateTime between '2010-01-01' and '2010-01-' with = "EXEC TC.TimeCardDateTime between '" & StartDate & "' AND '" & EndDate & "'"
where start and end dates are cells D2 and D3

When I run the VBA is get a runtime error '9' .. Subscript out of range.

I'm sure I'll get more errors once I get past this one.

Thanks any help on this!

 

[SkipVought]

First realize that your dates are really NUMBERS. faq68-5827

I don't have much experience with SQL Server databases, but I'm guessing that there's a function that converts a date STRING to a date NUMBER in SQL Server. Maybe ToDate()? Or maybe DateFromParts(), using Year, Month, Day.

At any rate a string is not what you gave in Excel and a string is not what you need in your criteria in your Where clause.


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

 

[Andrzejek]

Try:
[tt].CommandText = "EXEC TC.TimeCardDateTime between CAST('" & StartDate & "' AS DATETIME) AND CAST('" & EndDate & "' AS DATETIME)"[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

There ya go: I knew we'd flush out a real expert!

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

 

[olrac]

Hi , great I'll try that too. The problem with the "Script out of range" was that the ActiveWorkbook.Connections("MSP_RECON") was incorrectly named. I kept looking at problems with my datat type declarations Once this was changed the VBA worked perfectly.

Thanks to all for youe help!

Carlo

 

[Andrzejek]

real expert" not really, I was just doing this

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.