Help Required with SQL Statement Please!!!

[MXSVivien]

Hi,

I hope someone can help, I've been trying ro run this SQL query, but I keep getting an unspecified error message:

strSQL = "SELECT HourOfTheDay, BookingEngineStart, BookingEngineEnd, BookingEngineTime, OnlineCheckInStart," & _
"OnlineCheckInEnd, OnlineCheckInTime, BookingUpgradeStart, BookingUpgradeEnd, BookingUpgradeTime," & _
"FlyingClubStart, FlyingClubEnd, FlyingClubTime, FlyingCompanyStart, FlyingCompanyEnd, FlyingCompanyEnrolTime " & _
"from VAKPIs where StartDate >=#" & Format(dtStartDate, "dd/m/yyyy hh:mm") & "#" & _
"And EndDate <=#" & Format(dtEndDate, "dd/m/yyyy hh:mm") & "#" & _
" Order by HourOfTheDay"

I have tried different variations of this, but nothing seems to be working.

Can anyone help, Please????

Thank you in advance

Vivien

 

[PHV]

What about this ?

strSQL = "SELECT StartDate, EndDate, TimePeriod FROM VAKPIs "
strSQL = strSQL & "WHERE StartDate>='2004-11-01 00:00' AND EndDate<='2004-11-30 23:59'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MXSVivien]

Nope PHV, that didn't work either.

Thanks though...

What the dickens is wrong with it???????

I do really appreciate all of your help

But I am now tempted to throw my laptop out of the window, but unfortunately I can't because I'm hooked!!!! Goodness!

I'm not gonna have a good weekend if I don't get this sorted

 

[PHV]

that didn't work either
Which ODBC error message ?

 

[MXSVivien]

Run-time error '-2147467259 (80004005)':

Unspecified error

 

[DaveInIowa]

Since substituting the dates into the sql isn't working, how about trying it with parameters?

    strSQL = "SELECT HourOfTheDay, BookingEngineStart, BookingEngineEnd, BookingEngineTime, OnlineCheckInStart, " & _
            "        OnlineCheckInEnd, OnlineCheckInTime, BookingUpgradeStart, BookingUpgradeEnd, BookingUpgradeTime, " & _
            "        FlyingClubStart, FlyingClubEnd, FlyingClubTime, FlyingCompanyStart, FlyingCompanyEnd, FlyingCompanyEnrolTime " & _
            "   FROM VAKPIs " & _
            "  WHERE StartDate BETWEEN @StartDate AND @EndDate " & _
            "  ORDER BY HourOfTheDay "
            
    Set objCommand = New Command
    With objCommand
        .ActiveConnection = objConn
        .CommandText = strSQL
        .Parameters.Append .CreateParameter("@StartDate", adDate)
        .Parameters.Append .CreateParameter("@EndDate", adDate)
        .Parameters(0).Value = dtStartDate
        .Parameters(1).Value = dtenddate
    End With

 

[MXSVivien]

Thank you....

When do I set the recordset

Set objRecordSet = objCommand.Execute

I put it after the End With statement and got the same error.

 

[Ratsnackbar]

Thought I may be able to help.

I am no VB guy, found this thread while looking for something else. What I do know however is that Webtrends .wtd files are actually where they compress and encrypt the analyzed data. They are in a proprietary flat file format prior to that. So they are not something that you would normally be able to run a query directly against. You would need to install their ODBC Driver to do so.

The MS SQL Databases their UI uses are not for storage of analyzed data. Those store the UI configuration and details about the scheduled jobs. Depending on your usage, the actual analyzed data could easily produce more records then most enterprise databases could handle effectively so they do not have an SQL database for the analyzed data. At least not yet.

There is documentation on how to use the ODBC Driver that you can get through your 8.5 user interface by clicking on Customer Center > Resources > Product documentation. I found it in the right hand column about 2/3rds of the way down.

If you are using their On Demand service then it is generally better to just use their REST API

http://developer.webtrends.com

For a Software installation though the ODBC Driver is currently the only option.