When I run the below query I am prompted for the start year as well as the start and end week. I would also like to be prompted for route and product (which I know how to do), and have access allow me to enter multiple route and product numbers when prompted. Example 338,339,340 and/or 101,102,103. As well, wildcards will not work.
SELECT Int([route]/1000) AS Location, CLng([product]) AS ProductNum, DBo_LoadFulfillmentDaily.load_date, Sum(DBo_LoadFulfillmentDaily.load_qty) AS Load, Sum(IIf([adj_qty]>0,[adj_qty],0)) AS Adds, Sum(IIf([adj_qty]<0,[adj_qty],0)) AS Cuts, Sum([load_qty]+[adj_qty]) AS Final, Week2Date.Week
FROM Week2Date INNER JOIN DBo_LoadFulfillmentDaily ON Week2Date.YYYYMMDD = DBo_LoadFulfillmentDaily.load_date
GROUP BY Int([route]/1000), CLng([product]), DBo_LoadFulfillmentDaily.load_date, Week2Date.Week, Week2Date.Year
HAVING (((Int([route]/1000)) In (101)) AND ((CLng([product])) In (338)) AND ((Week2Date.Week)>=[Enter Start week 00] And (Week2Date.Week)<=[Enter End week 00]) AND ((Week2Date.Year)>=[Enter Start year 0000]))
ORDER BY Int([route]/1000), CLng([product]);