I'm fairly new with Access so please bare with me if I don't explain well what I am trying to accomplish.
I have a query that has several count expressions that I use to generate a report based on a Model Year. Because of the design of the query I don't believe I would be able to use parameters in the query itself. So I'm thinking I would create a query for each year that I can run to generate the information I need.
SELECT qryUnitCtsbyModel.MdlYr, tbl_Model.ModelID, tbl_Model.Model, Count(IIf([tbl_OrderDetails]![OnLot]=Yes,1,Null)) AS CountOnLot, Count(IIf([tbl_OrderDetails]![VIN] Is Null Or [tbl_OrderDetails]![OnLot]=True Or Not [tbl_OrderDetails]![DateSold] Is Null,Null,1)) AS CountIncoming, Count(IIf([tbl_OrderDetails]![OnLot] Is Null Or [tbl_OrderDetails]![DateSold]=True Or Not [tbl_OrderDetails]![VIN] Is Not Null,Null,1)) AS CountNoVIN, Count(IIf([qryUnitCtsbyModel]![BdyCdID] Is Null,Null,1)) AS CountTotOrderedUnits, Count(IIf([tbl_OrderDetails]![Action] Is Not Null,Null,IIf([tbl_OrderDetails]![DateSold] Is Null,Null,1))) AS CountTotalSold, Count(IIf([tbl_OrderDetails]![DateSold] Is Not Null And [tbl_OrderDetails]![Customer]="Cust",1,Null)) AS CountTradeInCustomer, Count(IIf([tbl_OrderDetails]![DateSold] Is Not Null And [tbl_OrderDetails]![Action]="DTO",1,Null)) AS CountTradeOut
FROM (tbl_Model LEFT JOIN qryUnitCtsbyModel ON tbl_Model.ModelID = qryUnitCtsbyModel.ModelID) LEFT JOIN tbl_OrderDetails ON qryUnitCtsbyModel.OrderID = tbl_OrderDetails.OrderID
GROUP BY qryUnitCtsbyModel.MdlYr, tbl_Model.ModelID, tbl_Model.Model
HAVING (((qryUnitCtsbyModel.MdlYr)="2015")) OR (((qryUnitCtsbyModel.MdlYr) Is Null))
ORDER BY tbl_Model.Model;
What I am wondering is, when I click the link on my Navigation form to open the report, is there a way via code or macro to select one of the queries by name and run it to generate the report? Or if there is another option? I hope that makes sense. Thank you.