Manipulating MS Query with VBA

[davie1982]

Hiya, this is pretty much Excel related.

I offered to use MS Access for what i'm doing but everyone prefers Excel.. here goes...

I have a query to the Sage SQL server. It brings back all open invoices. 62,000 lines and growing. Is there a way i could manipulate MS Query with VBA?

If so.. this is what i need:

I would like to change the filters on the query.
strCustomer would be the tables 'Customer' field.
strOP would be the tables 'open_indicator' field.

I've never manipulated any queries in VBA before, can you help me with this, or send me on to another resource for it?

Thanks

__________________

This can also be found here:
http://www.utteraccess.com/forums/s...1705458&page=0&view=collapsed&sb=5&o=&fpart=1

 

[davie1982]

OK, that works.

I've made my code here:

Sub test()
Dim strOpenClosed, strAccNo As String
2
strAccNo = InputBox("Enter the account number", "Account Number")

Select Case strAccNo
    Case Len(strAccNo) > 8
    MsgBox "Invalid Length"
    GoTo 2
End Select

strOpenClosed = InputBox("View what?" & vbNewLine _
    & "Open Items (O)" & vbNewLine _
    & "Closed Items (C)" & vbNewLine _
    & "All Items (A)", "Select")
1
Select Case strOpenClosed
    Case "A"
    strOpenClosed = ""
End Select

Select Case Len(strOpenClosed) > 1
    Case True
    MsgBox "Please enter the corresponding letter" & vbNewLine _
        & "Open = O, Closed = C, All = A"
    GoTo 1
End Select

Dim qt As QueryTable
Set qt = ActiveSheet.QueryTables(1)
qt.CommandText = "SELECT slitemm.customer," _
    & " slitemm.item_no, slitemm.refernce, " _
    & "slitemm.dated, slitemm.kind, slitemm.amount, " _
    & "slitemm.unall_amount, slitemm.open_indicator" & vbNewLine & _
    "FROM cs3live.scheme.slitemm slitemm" & vbNewLine & _
    "WHERE (slitemm.open_indicator='" & strOpenClosed & "' AND " _
    & " slitemm.customer='" & strAccNo & "')" & vbNewLine _
    & "ORDER BY slitemm.customer, slitemm.item_no"
qt.Refresh
End Sub

All is ok now, this is the solution i was looking for =P