VBA inputbox to excel sql queries

[bnpaco]

Hello,

I'm stuck at a roadblock with a report i'm writing. The report is a "template" that will be ran once a month every month and will look back at the previous month's data. What i've invisioned is writing a vb script to load on workbook open asking various questions via "inputbox()" method.

SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '20070731 10:10:51.450'
SET @EndDate = '20070731 10:11:51.450'
SET ROWCOUNT 100
SET NOCOUNT OFF
SELECT EventTagName = EventHistory.TagName, Value
 FROM EventHistory
INNER JOIN AnalogSnapshot ON EventHistory.EventLogKey = AnalogSnapshot.EventLogKey
INNER JOIN SnapshotTag ON SnapshotTag.SnapshotTagKey = AnalogSnapshot.SnapshotTagKey
 WHERE SnapshotTag.TagName IN ('SysSpaceMain')
 AND DateTime >= @StartDate
 AND DateTime <= @EndDate
 AND SnapshotTag.TagType = 1
 AND Quality = 0
SET ROWCOUNT 0

This is a generic query i created to use as an example of how i can use inputbox() and datepart() to create the startdate and enddate in my sql query. The day of the month must be static, however the year and month will not be.

dim querydate as Integer

Where querydate is my result of datepart() and inputbox().

how do i code querydate into each of my sql query definitions so when the workbook loads, it gets the information after the user has given the date to the inputbox()? The end result would be taking the 20070731 and breaking it to "querydate + 31".

I hope i've explained this clearly enough, running on nothing but coffee atm

I'm open to suggestions if i'm doing this the retarded/impossible/or hard way.

Thanks,

Marshall

 

[bnpaco]

Here's an explaination of the Excel Workbook.

There are query definitions used all over the book. This was done using: Data --> Import External Data --> New Database Query. So my queries have the database connection and the SQL code in a cell. When refreshed they run the query and return the data in that cell and populate down based on how many values the query returns.

This code is the section of my code that asks for user input. It asks for the Year and The month. Its very basic, but it gets the job done. It then stores that information in the variable 'querydate'.

Dim querymonth As String
Dim queryyear As String
Dim querydate As String

querymonth = InputBox("Please enter the Month for the Report")
queryyear = InputBox("Please enter the Year for the Report")
querydate = queryyear & querymonth & "31 10:10:51.450"

Now, using the example query i created yesterday,

SET NOCOUNT ON 
DECLARE @StartDate DateTime 
DECLARE @EndDate DateTime 
SET @StartDate = '20070731 10:10:51.450' 
SET @EndDate = '20070731 10:11:51.450' 
SET ROWCOUNT 100 
SET NOCOUNT OFF 
SELECT EventTagName = EventHistory.TagName, Value 
 FROM EventHistory 
INNER JOIN AnalogSnapshot ON EventHistory.EventLogKey = AnalogSnapshot.EventLogKey 
INNER JOIN SnapshotTag ON SnapshotTag.SnapshotTagKey = AnalogSnapshot.SnapshotTagKey 
 WHERE SnapshotTag.TagName IN ('SysSpaceMain') 
 AND DateTime >= @StartDate 
 AND DateTime <= @EndDate 
 AND SnapshotTag.TagType = 1 
 AND Quality = 0 
SET ROWCOUNT 0

You will notice that if you add MsgBox(querydate) to my VBA code and run it and put in a 2 digit month and 4 digit year, you get something like '20070731 10:10:51.450'. My SQL code uses

@StartDate = '20070731 10:10:51.450'

. Thats a static date, i do not want that. However, if I could use the results in the variable 'querydate' and somehow transfer it to my SQL query definition; for example:

@StartDate = querydate

so that way when the Excel workbook is refreshed the @startdate will have the value from the variable 'querydate'. This makes it a Dynamic report that never has to be edited by an operator that he can run every month and get the correct values.

My problem is that i cant just type @Startdate = querydate. The SQL definition doesnt recognize my VBA variable. What i'm missing is the correct method to get it to work.

 

[bnpaco]

Anyone know the magic code i'm missing to get it to work?

I dont see much SQL mention here, i'm sorry if i posted this on the wrong forums, i've got similar posts elsewhere but no responses yet. Is there a more common way of going about what i want to do that i'm missing?

 

[QuietRiot]

you try this already?

http://www.ozgrid.com/forum/forumdisplay.php?f=31

 

[bnpaco]

Yes i've searched over there too, however their rules had stated not to multiforum post and I did not want to do so. I have since posted the same question there as I did here.

If anyone here does come across the solution to my problem, I would still be greatly appreciated.

 

[bnpaco]

Does anyone knwo where i can go to get a solution? i've tried for weeks now and no luck.

 

[Norie]

I'm going to ask for the last time, can you actually post the whole code?

You've only posted the SQL and a few lines of VBA.

Surely there must be more.

 

[bnpaco]

Norie,

I dont know what to say, thats all of it.

the vba i posted is short but sweet. It gets the date via user input and puts it into the format my SQL query definitions recognize.

All thats left to do is to somehow get the contents of 'querydate' into each of the query definitions. Now if there's more vba code required to put it into my sql definitions, thats where i'm at a loss.