Dim SQLString As String
 
SQLString = "SELECT Orders.SupplierID, Suppliers.CompanyName, Sum([Quantity]*[UnitPrice]) AS TotalAmt, Count(Orders.OrderID) AS CountOfOrderID " 
SQLString = SQLString & "FROM Orders LEFT JOIN Suppliers ON Orders.SupplierID = Suppliers.SupplierID " 
SQLString = SQLString & "WHERE Year([OrderDate])= " & Inputbox("Please enter the year for the annual report:") 
SQLString = SQLString & " GROUP BY Orders.SupplierID, Suppliers.CompanyName"

Dim SQLString As String
 
SQLString = "SELECT Orders.OrderID, Orders.Product, Orders.UnitPrice, Orders.Quantity, [Quantity]*[UnitPrice] AS TotalAmt, Orders.OrderDate, Orders.Received, Orders.SupplierID, Suppliers.CompanyName " 
SQLString = SQLString & "FROM Orders LEFT JOIN Suppliers ON Orders.SupplierID = Suppliers.SupplierID " 
SQLString = SQLString & "WHERE [Quantity]*[UnitPrice] >=  " & InputBox("Please enter the order total to report on:")

Private Sub Command1_Click()
   Dim Prod As Connection
   Dim rs As Recordset
   Dim i As Integer
 
   Set Prod = New ADODB.Connection
   Prod.Provider = "Microsoft.Jet.OLEDB.4.0;"
'NEVER Hard-Code a path unless you have a VERY good reason for doing this.
'Rather use the app.path to see where the application currently is
'   Prod.ConnectionString = "C:\VB Assignment Uni\Assignment\OK.mdb"
   Prod.ConnectionString = App.Path & "\OK.mdb"
   Prod.Open
   Set rs = New ADODB.Recordset
 
   rs.Open "SELECT Orders.SupplierID,Suppliers.CompanyName,Sum([Quantity]*[UnitPrice]) AS TotalAmt,Count(Orders.OrderID) As CountOfOrderID " & _
           "FROM Orders LEFT JOIN Suppliers ON Orders.SupplierID = Suppliers.SupplierID " & _
           "Where Year([OrderDate]) = 2003 " & _
           "GROUP BY Orders.SupplierID, Suppliers.CompanyName", Prod, adOpenDynamic, adLockOptimistic
 
   Set xlApp = New Excel.Application
   'Remove the following line to hide Excel, I want to see it for debugging
   xlApp.Visible = True
   Set xlWkb = xlApp.Workbooks.Add
   Set xlsht = xlWkb.Worksheets(1)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 02 24 2006 by Yomet
'
 
'
    Charts.Add
    ActiveChart.ChartType = xlBarClustered
    ActiveChart.SeriesCollection(1).Name = "=""Sales (* 1000$)"""
    ActiveChart.SeriesCollection(2).Name = "=""Orders"""
    ActiveChart.Location Where:=xlLocationAsNewSheet
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Annual Report"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Company"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales / Orders"
    End With
End Sub