MSQuery Pass Variable

[paddysheeran]

Hi All,

After a couple of hours of searching and not been able to find any solutions can anyone help with the below:

My Query is:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Lancaster;UID=gcs_bo_rep;;DBQ=LANCASTER.WORLD;DBA=W;APA=T;EXC=F;FE N=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllS" _
), Array("uccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=0;")), _
Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT TYS____FAULT.SEVERITY, TYS____FAULT.OLD_FAULT_ID, TYS____FAULT.SITE, TYS____FAULT.INCIDENT_START_TIME, TYS____FAULT.INCIDENT_END_TIME, TYS____FAULT.TOTAL_OUTAGE_TIME__SECS_, TYS____FAULT.FAULT_" _
, _
"ENQUIRY_DESCRIPTION, TYS____FAULT.ELEMENT, TYS____FAULT.INCIDENT_CLOSED_DATE, TYS____FAULT.STATUS, TYS____FAULT.CUSTOMERS_NAME, TYS____FAULT.CLOSED_BY, TYS____FAULT.CID" & Chr(13) & "" & Chr(10) & "FROM ARADMIN.TYS____FAULT TYS_" _
, _
"___FAULT" & Chr(13) & "" & Chr(10) & "WHERE (TYS____FAULT.INCIDENT_START_TIME>'" & Date_Search & "'" & " AND (TYS____FAULT.CID='CUS48628')" & Chr(13) & "" & Chr(10) & "ORDER BY TYS____FAULT.INCIDENT_START_TIME" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_Lancaster"
.Refresh BackgroundQuery:=False
End With

When I run this I get an error message "Run-time error '1004' - General ODBC Error". I know this is down to the variable Date_Search as when I use a fixed number the query works fine.

Any help would be greatly appreciated.

thanks,

Paddy.

[Crocus Crow]

What data type is Date_Search? If it's Date and a fixed number works, try CLng(Date_Search).
If no joy, what database server is this? Oracle, SQL Server, etc? And what is the data type of the TYS____FAULT.INCIDENT_START_TIME column?

[paddysheeran]

Date Search data type is long as the date has to be entered as epoch time and its Oracle. I think I may need to pass the variable to a parameter but not sure how to do this?

[paddysheeran]

could I assign the command text query to a string variable?

[Crocus Crow]

I don't have Oracle, so can't really help much more, sorry.

Assigning the SELECT statement (the command text) to a string first won't make any difference because VBA seems to convert the array elements to a single string anyway - CommandText is a variant/string type as shown in the Locals window in the VB editor. As an aside, I'm not sure how the use of the Array function for the CommandText property (and other properties like the Connection property) results in a string and haven't seen any documentation for this.

Looks like you need the Oracle To_Date function - http://www.ozgrid.com/forum/showthread.php?t=143525

[Kenneth Hobs]

See if these help:
http://www.vbaexpress.com/forum/showthread.php?t=24118
http://www.vbaexpress.com/forum/showthread.php?t=24575
http://www.vbaexpress.com/forum/showthread.php?t=23783
http://www.vbaexpress.com/forum/showthread.php?t=26145