Hi all,

I'm having trouble with passing two variables into a SQL Statement. Using the following code i get an invalid column error. If I hardcode the dates, the query works fine. Any help would be greatly appreciated. Thanks.

'Set up connection to Oracle

Private Sub CommandButton1_Click()
Dim Conn As String
    Dim drv As String
    uid = "myUserName"
    pwd = "myPassword"
    dBase = "myDatabase"
    Set db = New ADODB.Connection

                Conn = "UID= " & uid & ";PWD=" & pwd & ";DRIVER={Microsoft ODBC For Oracle};" _
                & "SERVER=" & dBase & ";"

        'Screen.MousePointer = vbHourglass
        'Connect With ORACLE
        With db
            .ConnectionString = Conn
            .CursorLocation = adUseClient
            .Open
        End With
QueryDate
End Sub


'Perform the query and write the results into an Excel worksheet
Sub QueryDate()
Dim StartDate As String
Dim EndDate As String

StartDate = DTPicker1.Value
EndDate = DTPicker2.Value
StartQuery = Format(StartDate, "dd-" & "MMMM-" & "yyyy")
EndQuery = Format(EndDate, "dd-" & "MMMM-" & "yyyy")
StartQuery = UCase(StartQuery)
EndQuery = UCase(EndQuery)
BillingPeriod.Hide
MsgBox StartQuery


Dim i As Integer
sSql = "select unique wa_number, title from hours_cost_view where billing_date > StartQuery and billing_date < EndQuery"


'I have also tried this approach
'sSql = "select unique wa_number, title from hours_cost_view where billing_date > " & StartQuery & "and billing_date <" & EndQuery &""
Set rs = Nothing
rs.Open sSql, db, adOpenDynamic, adLockOptimistic


If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
i = 1
Do Until rs.EOF

Range("A2").CopyFromRecordset rs

Loop
rs.Close
End If
End Sub

I was able to figure out where I erred and thought I'd post it back for other users. I simply needed to quote the single quotes around the date variables. See below:

Sub QueryDate()
Dim StartDate As String
Dim EndDate As String
Dim StartQuery As String
Dim EndQuery As String
StartDate = DTPicker1.Value
EndDate = DTPicker2.Value
StartQuery = Format(StartDate, "dd-" & "MMMM-" & "yyyy")
EndQuery = Format(EndDate, "dd-" & "MMMM-" & "yyyy")
StartQuery = UCase(StartQuery)
EndQuery = UCase(EndQuery)
BillingPeriod.Hide


Dim i As Integer
sSql = "select unique wa_number, title from hours_cost_view where billing_date >" & "'" & StartQuery & "' " & "and billing_date < " & "'" & EndQuery & "'" & ""
'sSql = "Select * from unique_inprogress_view where not exists(select null from unique_routed_wam_view where unique_inprogress_view.wa_number = unique_routed_wam_view.WA_NUMBER)"
Set rs = Nothing
rs.Open sSql, db, adOpenDynamic, adLockOptimistic


If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
i = 1
Do Until rs.EOF

Range("A2").CopyFromRecordset rs

Loop
rs.Close
End If
End Sub