Working in latest version of Access and trying to create a stacked bar chart in VBA. Got it partially working, but having some troubles. See some code below. It is not working correctly, just my last attempt. Can't figure out how to incorporate the COUNT of a value (trackingnumber) which is how I determine number of packages shipped. Attached is the correct I use currently by using the wizard, I would like to move it to code to allow for more flexibility at runtime.
str = "SELECT Orders.OrderID, Orders.ShipDate, Orders.PackedBy, Orders.Cancelled, UPSTrackingNumbers.TrackingNumber, Orders.Shipped, UPSTrackingNumbers.ShippingMethod FROM Orders INNER JOIN UPSTrackingNumbers ON Orders.OrderID = UPSTrackingNumbers.OrderNo WHERE (((Orders.ShipDate)=Date()) AND ((Orders.Cancelled)=False) AND ((Orders.Shipped)=True))"
With objchart
.ChartType = acChartBarStacked
.ChartTitle = "Total Orders Shipped by Packer"
.RowSource = str
.ChartAxis = "[ShippingMethod];[PackedBy]"
.ChartValues = "ShipDate" '"PackedBy"
.ChartLegend = "ShipDate"
End With
Thank you,
Ira
str = "SELECT Orders.OrderID, Orders.ShipDate, Orders.PackedBy, Orders.Cancelled, UPSTrackingNumbers.TrackingNumber, Orders.Shipped, UPSTrackingNumbers.ShippingMethod FROM Orders INNER JOIN UPSTrackingNumbers ON Orders.OrderID = UPSTrackingNumbers.OrderNo WHERE (((Orders.ShipDate)=Date()) AND ((Orders.Cancelled)=False) AND ((Orders.Shipped)=True))"
With objchart
.ChartType = acChartBarStacked
.ChartTitle = "Total Orders Shipped by Packer"
.RowSource = str
.ChartAxis = "[ShippingMethod];[PackedBy]"
.ChartValues = "ShipDate" '"PackedBy"
.ChartLegend = "ShipDate"
End With
Thank you,
Ira