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

 

[HarleyQuinn]

If you put a break in the code after you assign strSQL and then Debug.Print it can you run the query returned?

Also, not sure if this will cause the problem but you could do with putting a space at the start of the "And EndDate...
line.

Hope this helps

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[MakeItSo]

Hi Vivien,

at first glance, the only error I can identify is that you are missing a space before "And EndDate" resp. after the closing "#" of the start date.

If the error persists, you need to give more information, i.e. what kind of database you connect to (Access, I presume), how you do it (ADO? DAO?), whether your connection is opened etc.
Best: some code.
;-)

Cheers,
Andy


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SkipVought]

Hi,

This helps me understand my SQL code better...

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

Also FORMAT converts Date to String, then the # delimeter converts the String to a Date. WHY???

    sSQL = sSQL & "where StartDate >=" & dtStartDate
    sSQL = sSQL & "  And EndDate <=" & dtEndDate

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Skip:
sSQL = sSQL & "where StartDate >=" & dtStartDate
dtStartDate is implicitly converted to string due the & operator.

Vivien:
use non ambiguous date:
Format(dtStartDate, "yyyy-mm-dd hh:nn")

 

[MXSVivien]

Hi,

Thanks Andy & Harley,

Here's the code. Basically I'm trying retrieve WebTrends data using ODBC. I believe it is stored in an SQL Server database. I have been able to retrieve data from the database using string variables, but for some reason the date variables are giving me a headache. As you can see I have changed the query slightly since the last post, as I believe I don't need # when I'm working with an SQL Server database. I also entered a space as suggested, but that didn't work either Please help!



Dim dtStartDate As Date
Dim dtEndDate As Date
Dim I As Integer

'check that the user enters a valid date
txtStartDate.SetFocus
If (txtStartDate.Text = "") Or IsDate(txtStartDate.Value) = False Then
MsgBox "Please enter a valid start date."
Exit Sub
End If

txtEndDate.SetFocus
If (txtEndDate.Text = "") Or IsDate(txtEndDate.Value) = False Then
MsgBox "Please enter a valid start date."
Exit Sub
End If


strProfile = "jNv2LJL0Hf6.wlp"
strProfileName = "VA"

'now get the details and enter onto spreadsheet
Dim objConn As ADODB.Connection
Dim objCommand As ADODB.Command
Dim objRecordSet As ADODB.Recordset



Set objConn = New ADODB.Connection
Set objCommand = New ADODB.Command
Set objRecordSet = New ADODB.Recordset


objConn.Open "Driver={WebTrends ODBC Driver};DATABASE=;SERVER=;PORT=;AccountId=;UID;PASSWORD=;SSL=0;ProfileGuid=" + strProfile + ";"

NOTE:I pulled out all this info for security purposes


objCommand.ActiveConnection = objConn

dtStartDate = CDate(txtStartDate)
dtEndDate = CDate(txtStartDate)

'Display Date being reported
Sheets(1).Select

For I = 1 To 20
Columns(I).ColumnWidth = 20
Next I

Range("A1") = strProfileName
Range("A1").Font.Bold = True
Range("A4") = "Report Period: " + Format(dtStartDate, "dd-mmm-yyyy") + " - " + Format(dtEndDate, "dd-mmm-yyyy")
Range("A4").Font.Bold = True

objRecordSet.CursorLocation = adUseClient
objRecordSet.CursorType = adOpenForwardOnly
objRecordSet.LockType = adLockReadOnly


strSQL = "SELECT HourOfTheDay, BookingEngineStart, BookingEngineEnd, BookingEngineTime, OnlineCheckInStart," & _
"OnlineCheckInEnd, OnlineCheckInTime, BookingUpgradeStart, BookingUpgradeEnd, BookingUpgradeTime," & _
"FlyingClubStart, FlyingClubEnd, FlyingClubTime, FlyingCompanyStart, FlyingCompanyEnd, FlyingCompanyEnrolTime " & _
"from VAKPIs where StartDate >='" & dtStartDate & "'" & _
" And EndDate <='" & dtEndDate & "'" & _
" Order by HourOfTheDay"

objCommand.CommandText = strSQL

(This is where it all goes to pot, and I get the unspecified error!)

Set objRecordSet = objCommand.Execute

 

[MXSVivien]

Thank you Skip, I just tried this, but still no joy:

strSQL = "SELECT HourOfTheDay, BookingEngineStart, BookingEngineEnd, BookingEngineTime, OnlineCheckInStart," & _
"OnlineCheckInEnd, OnlineCheckInTime, BookingUpgradeStart, BookingUpgradeEnd, BookingUpgradeTime," & _
"FlyingClubStart, FlyingClubEnd, FlyingClubTime, FlyingCompanyStart, FlyingCompanyEnd, FlyingCompanyEnrolTime " & _
"from VAKPIs where StartDate >=" & dtStartDate & _
" And EndDate <=" & dtEndDate & _
" Order by HourOfTheDay

 

[SkipVought]

I misslead you regarding the dates,as PHV pointed out. Where was my head?

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

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[MXSVivien]

That hasn't worked either Skip

 

[Remou]

As far as I can tell, you need single quotes for SQL server dates:

'20061030'

http://lessthandot.com

 

[MXSVivien]

I just tried this and it didn't work either

strSQL = "SELECT HourOfTheDay, BookingEngineStart, BookingEngineEnd, BookingEngineTime, OnlineCheckInStart," & _
"OnlineCheckInEnd, OnlineCheckInTime, BookingUpgradeStart, BookingUpgradeEnd, BookingUpgradeTime," & _
"FlyingClubStart, FlyingClubEnd, FlyingClubTime, FlyingCompanyStart, FlyingCompanyEnd, FlyingCompanyEnrolTime " & _
"from VirginAtlanticKPIs where StartDate >='01/11/2004'" & _
" And EndDate <='01/11/2004'" & _
" Order by HourOfTheDay"

I'm going to start crying in a minute

 

[Remou]

Don't cry just yet.

Did you try:

" And EndDate <='20041101'" & _

What is the field type in SQL Server?

http://lessthandot.com

 

[PHV]

You have to use T-SQL syntax.

 

[Remou]

(or '2000-12-13')

http://lessthandot.com

 

[MXSVivien]

Hi Remou,

I tried your first suggestion a while back, that didn't work
and I just tried your second suggestion

strSQL = "SELECT HourOfTheDay, BookingEngineStart, BookingEngineEnd, BookingEngineTime, OnlineCheckInStart," & _
"OnlineCheckInEnd, OnlineCheckInTime, BookingUpgradeStart, BookingUpgradeEnd, BookingUpgradeTime," & _
"FlyingClubStart, FlyingClubEnd, FlyingClubTime, FlyingCompanyStart, FlyingCompanyEnd, FlyingCompanyEnrolTime " & _
"from VirginAtlanticKPIs where StartDate >= ('01/11/2004') And EndDate <= ('01/11/2004') Order by HourOfTheDay"

and this

strSQL = "SELECT HourOfTheDay, BookingEngineStart, BookingEngineEnd, BookingEngineTime, OnlineCheckInStart," & _
"OnlineCheckInEnd, OnlineCheckInTime, BookingUpgradeStart, BookingUpgradeEnd, BookingUpgradeTime," & _
"FlyingClubStart, FlyingClubEnd, FlyingClubTime, FlyingCompanyStart, FlyingCompanyEnd, FlyingCompanyEnrolTime " & _
"from VirginAtlanticKPIs where (StartDate >='01/11/2004') (And EndDate <='01/11/2004') Order by HourOfTheDay"

Neither worked

I have tried to access the data via SQL directly but it looks like the data is stored in WTD files which I presume are WebTrends Data files, which cannot be read unless I use ODBC or some kind of API I believe.

I never been so stuck on something in my life, I don't have a clue how to fix this....

Surely it's time to cry now Remou... when even the experts can't help me... and there is absolutely no way around it


Now I'm going to try and find out if PHVs suggestion will help me although I don't have a clue what it is...

Any suggestions or examples I can try will be greatly appreciated.

 

[PHV]

OOps, sorry, I know NOTHING about WebTrends ODBC Driver ...

 

[MXSVivien]

Seeing as SQL is used to retrieve the data, I would have assumed that WebTrends would use similar data types?

 

[Remou]

Just wipe a small tear from your eye and gulp bravely.

It seems that Webtrends may use MySQL. A few thoughts. Can you run a very simple query (SELECT * FROM tablename)? Is the Webtrends knowledge base of no use?

http://lessthandot.com

 

[MXSVivien]

LOL Remou... I'm trying not to, it's just very frustrating when you are up against a brick wall

I'm using Version 8.5 that uses MS SQL, the older versions used MySQL

Report data isn't stored in SQL though it's stored in the WTD files

I've looked in the knowledgebase,there nothing of help there

Right now I'm trying to retrieve the data types of the Start and End Date fields and see if that helps at all

And yes I am able to run queries using other fields but just not these date fields and i can't for the life of me work out why.

But Alas.... I never give up even through the tears

Back soon...

 

[MXSVivien]

Right....

I created simple query

strSQL = "SELECT StartDate, EndDate, TimePeriod From VAKPIs"

I've run a check on the field type for the StartDate and EndDate fields and it returned a value of 135, checking this I found that they are ADDBTIMESTAMP fields.

Checking the values in VBA, in break mode I find that they are in the formats 01/11/2004 and 30/11/2004 23:59
with no quotes or #s surrounding them.

Any ideas on what I should do now to get this working?

Thanks