Sybase - SQL get data between dates ( cell ref )

DaveWillett

New Member
Joined
Apr 27, 2013
Messages
1
Hi. I'm unsuccessful getting data between two cell references. The vba is as below and have tried several alternatives. I either get errors saying cannot convert to timestamp etc. Would a user be kind enough to assist? The code I'm trying to run is commented out below starting with:

'rst.Open Source:="SELECT * FROM DBA.INVOICE_DETAIL_VIEW WHERE InvDate >= '" & _
' Format(Worksheets("Dashboard").Range("I3").Value) & "' AND InvDate <= '" & _
' Format(Worksheets("Dashboard").Range("I4").Value) & "'", _
' ActiveConnection:=cnMTPS, Options:=adCmdText

Code:
Sub RunInvoiceData()    Dim cnMTPS As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim i As Long
    Dim SQL1 As String
    
    Dim RowNumber As Integer
    RowNumber = Worksheets("InvoiceData").Range("A65536").End(xlUp).Row
    
    'RowNumber = ActiveSheet.Range("A65536").End(xlUp).Row
    Range("InvoiceData!A1:CC0" & RowNumber).ClearContents
       
    ' Open connection to database
    Set cnMTPS = New ADODB.Connection
    cnMTPS.Open ConnectionString:="autoclaim"
    ' Open recordset on table
    Set rst = New ADODB.Recordset
    
    rst.Open Source:="SELECT * FROM DBA.INVOICE_VIEW where datediff(month, InvDate, GETDATE()) < " & Worksheets("Dashboard").Range("E4").Value, _
    ActiveConnection:=cnMTPS, Options:=adCmdText
    
    'rst.Open Source:="SELECT * FROM DBA.INVOICE_DETAIL_VIEW WHERE InvDate >= '" & _
    '   Format(Worksheets("Dashboard").Range("I3").Value) & "' AND InvDate <= '" & _
    '    Format(Worksheets("Dashboard").Range("I4").Value) & "'", _
    '    ActiveConnection:=cnMTPS, Options:=adCmdText


    
    ' Enter field names in first row
    For i = 1 To rst.Fields.Count
        Worksheets("InvoiceData").Cells(, i).Value = rst.Fields(i - 1).Name
    Next i
    
    ' Copy records to worksheet, starting at cell A2
    Worksheets("InvoiceData").Range("A2").CopyFromRecordset rst
    ' Close the recordset and the connection
    rst.Close
    cnMTPS.Close
    Set cnMTPS = Nothing
    
End Sub
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Watch MrExcel Video

Forum statistics

Threads
1,122,553
Messages
5,596,806
Members
414,103
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top