date criteria

[farrukh]

HI All,

I need some help in my code , the code works fine when i just select * from table give the results from that table .But when i pass date crietia it does not reterive any data in excel sheet ?

Help me out passing the date in this sql query


[VBA]Global sConnectStr As String
Dim dataGetter As New Data

Sub testconnection()
Dim subArray(0 To 5) As Variant
subArray(0) = "CONNECTION"
subArray(1) = "Provider=sqloledb;Network Library=DBMSSOCN;Data Source=localhost,1433;Initial Catalog=DB;User ID=user;Password=pass;Application Name=AVM"




subArray(2) = "FROM_DATETIME"
subArray(2) = "AS_OF_DATE"
subArray(3) = CDate("2/1/2011")
subArray(4) = "TO_DATETIME"
subArray(5) = CDate("5/4/2011")
MainExec subArray
End Sub
Sub MainExec(rawParams() As Variant)
Dim dStartDate As Date
Dim params As New Collection
Dim iii As Integer

For iii = 0 To UBound(rawParams) - 1
params.Add rawParams(iii + 1), rawParams(iii)
MsgBox rawParams(iii + 1) & " - " & rawParams(iii)
iii = iii + 1
Next iii

sConnectStr = "Provider=sqloledb;" & params("CONNECTION")
dStartDate = CDate(params("AS_OF_DATE"))
dEndDate = CDate(params("TO_DATETIME"))

Sheet1.Cells(2, 3) = dStartDate
Sheet1.Cells(2, 5) = dEndDate

Call FillNetworks(dStartDate)

Call CompData(dStartDate)

End Sub
Sub FillNetworks(dAsOfDate As Date)

Dim sColumnName As String
Dim Range As Excel.Range
Dim valForm As String

Dim sFieldSql As String


'*******Find Networks
Call dataGetter.OpenConnection(sConnectStr)
Call dataGetter.FillNetworks("NETWORK", dAsOfDate, 1, "O1", 100, 1)
Call dataGetter.CloseConnection

' *******And updateselection list
' Sheet1.Range("G2") = Null
' Set Range = Sheet1.Range("G2")
' valForm = "=$O$1:$O$40"
' Range.Validation.Delete
' Range.Validation.Add Type:=Excel.XlDVType.xlValidateList, AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, Operator:=Excel.XlFormatConditionOperator.xlBetween, Formula1:=valForm
' Sheet1.Range("G2").Value = Sheet1.Range("O1").Value
End Sub

Sub CompData(dAsOfDate As Date)
Dim wSheet As Worksheet
Dim wProd As Worksheet
Dim dStartDate As Date
Dim dEndDate As Date
Dim sql As String
Dim sPotRangeUL As String
Dim dStartMonth As Date
Dim dStartYear As Date

sPotRangeUL = "A2"
'*******Get input data
dStartDate = DateValue(dAsOfDate)
dEndDate = dStartDate + 1
dStartMonth = DateValue("1-" & Month(dStartDate) & "-" & Year(dStartDate))
dStartYear = DateValue("1-jan" & "-" & Year(dStartDate))
Set wSheet = Worksheets("TEST")


'*******Open Connection
Call dataGetter.OpenConnection(sConnectStr)
sql = "select * FROM table" '& _
' "WHERE(STARTDATE >= '" & Format(dStartDate, "m/d/yyyy HH:MM:SS") & "') And (START_DATE < '" & Format(dStartDate + 1, "m/d/yyyy HH:MM:SS") & "')"

Application.DisplayAlerts = False
'*******Fill data
Call dataGetter.GetOperFieldRead(dStartDate, dEndDate, sql, wSheet.Index, sPotRangeUL, 0, 100)

Set recReportData = Nothing
Application.DisplayAlerts = True
Call dataGetter.CloseConnection


End Sub[/VBA]

Thanks
frexzz

[mohanvijay]

try this

[vba]
"WHERE FROMAT(STARTDATE,""yyyymmdd"")>='" & Format(dStartDate,"yyyymmdd") & "' And..."
[/vba]

[mdmackillop]

Hi Farrukh
FYI, use the green VBA button to format your code as shown, rather than the "code" tags

[farrukh]

HI mdmackillop,

Sorry i cannot use the green VBA button to format the code there is no edit option available?

@mohanvijay dear it does not work the syntax you have pass to me...

Thanks
frexzz